/****** Object:  UserDefinedFunction [dbo].[ConcatFunction]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP FUNCTION [dbo].[ConcatFunction]
GO
/****** Object:  StoredProcedure [dbo].[tool_SelectKeHoachNotInDonViChiTieu]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[tool_SelectKeHoachNotInDonViChiTieu]
GO
/****** Object:  StoredProcedure [dbo].[spStatus_GetByID]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spStatus_GetByID]
GO
/****** Object:  StoredProcedure [dbo].[spStatus_GetAll]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spStatus_GetAll]
GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNhomDonVi_InsertUpdate]
GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNhomDonVi_GetListPaging]
GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_GetByType]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNhomDonVi_GetByType]
GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_GetById]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNhomDonVi_GetById]
GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_GetByDonViID]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNhomDonVi_GetByDonViID]
GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_GetAll]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNhomDonVi_GetAll]
GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_Delete]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNhomDonVi_Delete]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungDonVi_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDungDonVi_Insert]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungDonVi_GetListUser]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDungDonVi_GetListUser]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungDonVi_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDungDonVi_GetListPaging]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungDonVi_Delete]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDungDonVi_Delete]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungChiTieu_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDungChiTieu_Insert]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungChiTieu_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDungChiTieu_GetListPaging]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungAdmin_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDungAdmin_GetListPaging]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDung_InsertUpdate]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDung_GetListPaging]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_GetById]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDung_GetById]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_GetAdminById]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDung_GetAdminById]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_Get_PM2]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDung_Get_PM2]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_Get_List_DonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDung_Get_List_DonVi]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_Get_List_ChiTieu]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDung_Get_List_ChiTieu]
GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_Delete]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNguoiDung_Delete]
GO
/****** Object:  StoredProcedure [dbo].[spNamkehoch_Getbefore]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNamkehoch_Getbefore]
GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoachChiTieu_Update]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNamKeHoachChiTieu_Update]
GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoachChiTieu_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNamKeHoachChiTieu_Insert]
GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoach_Update]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNamKeHoach_Update]
GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoach_CheckPhanHoi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNamKeHoach_CheckPhanHoi]
GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoach_CheckChot]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNamKeHoach_CheckChot]
GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoach_Check]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spNamKeHoach_Check]
GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_IS_ACTIVE]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spMocKiemSoat_IS_ACTIVE]
GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spMocKiemSoat_InsertUpdate]
GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_GetByID_NEW]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spMocKiemSoat_GetByID_NEW]
GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_GetByID]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spMocKiemSoat_GetByID]
GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_GetBefo]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spMocKiemSoat_GetBefo]
GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_DELETE]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spMocKiemSoat_DELETE]
GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_CheckThucHien]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spMocKiemSoat_CheckThucHien]
GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_CheckBanChot]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spMocKiemSoat_CheckBanChot]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_Update]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_Update]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_InsertUpdate]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_Insert]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_GetListPaging]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_GetById]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_GetById]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_Delete]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_Delete]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieuRieng_DonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_ChiTieuRieng_DonVi]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieu_Sum_TheoNhomDonVi_DHQG]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_ChiTieu_Sum_TheoNhomDonVi_DHQG]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieu_Sum]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_ChiTieu_Sum]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieu_DonVi_TheoNhomDonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_ChiTieu_DonVi_TheoNhomDonVi]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieu_DonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoachThucHien_ChiTieu_DonVi]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_UpdateCURRENT]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_UpdateCURRENT]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_Rieng_Getall_by_nkh]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_Rieng_Getall_by_nkh]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_Thuchien_HuychotbyDONVI]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_Thuchien_HuychotbyDONVI]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_GetByNamKeHoach_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_GetByNamKeHoach_SUM]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_GetByNamKeHoach_NhomDonViID_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_GetByNamKeHoach_NhomDonViID_SUM]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_GetByNamKeHoach_CAP1_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_GetByNamKeHoach_CAP1_SUM]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_GetByNamKeHoach_BaoCao]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_GetByNamKeHoach_BaoCao]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_Thuchien_GetByNamKeHoach]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_Thuchien_GetByNamKeHoach]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_GetByDV]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_GetByDV]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_Getall_by_nkh_mockiemsoat]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_Getall_by_nkh_mockiemsoat]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_Getall_by_nkh]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_Getall_by_nkh]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_DeleteByNKH]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_DeleteByNKH]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_CheckFeedBack]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_CheckFeedBack]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_Thuchien_CheckChotbyDONVI]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_Thuchien_CheckChotbyDONVI]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_Checkchot]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ThucHien_Checkchot]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_SUM_CANDOI]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_SUM_CANDOI]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_Rieng_GetByNamKeHoach]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_Rieng_GetByNamKeHoach]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_KiemTraChiTieu]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_KiemTraChiTieu]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_InsertUpdate]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_Insert_TABLE]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_Insert_TABLE]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_HuyChot]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_HuyChot]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_GetByNamKeHoach_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_GetByNamKeHoach_SUM]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_GetByNamKeHoach_StatusId_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_GetByNamKeHoach_StatusId_SUM]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_GetByNamKeHoach_CAP1_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_GetByNamKeHoach_CAP1_SUM]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_GetByNamKeHoach]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_GetByNamKeHoach]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_GetByDV]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_GetByDV]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_DeletePhanHoi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_DeletePhanHoi]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_DeleteByNKH]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_DeleteByNKH]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_DeleteByDONVICHITIEU]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_DeleteByDONVICHITIEU]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_DanhSachDieuChinh]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_DanhSachDieuChinh]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_Chot_DieuchinhGiuanam]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_Chot_DieuchinhGiuanam]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieuRieng_DonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_ChiTieuRieng_DonVi]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_TheoNhomDonVi_DHQG]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_ChiTieu_TheoNhomDonVi_DHQG]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_TheoDonVi_Sum]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_ChiTieu_TheoDonVi_Sum]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_Sum_old]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_ChiTieu_Sum_old]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_Sum_20160522]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_ChiTieu_Sum_20160522]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_Sum]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_ChiTieu_Sum]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_DonVi_TheoNhomDonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_ChiTieu_DonVi_TheoNhomDonVi]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_DonVi___test]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_ChiTieu_DonVi___test]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_DonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_ChiTieu_DonVi]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ChinhThuc_ByNamKeHoach]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_ChinhThuc_ByNamKeHoach]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_CheckUpdateDieuchinh]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_CheckUpdateDieuchinh]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_CheckPhaHoiChot]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_CheckPhaHoiChot]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_CheckChotByNhomDV]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_CheckChotByNhomDV]
GO
/****** Object:  StoredProcedure [dbo].[spKehoach_Checkchot]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKehoach_Checkchot]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_Ban_Phu_Trach_TheoDonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_Ban_Phu_Trach_TheoDonVi]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_Ban_Phu_Trach]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spKeHoach_Ban_Phu_Trach]
GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_Rieng_DeleteByNKH]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonViChiTieu_Rieng_DeleteByNKH]
GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonViChiTieu_Insert]
GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_GetByDonvi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonViChiTieu_GetByDonvi]
GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_GetAllByNKH]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonViChiTieu_GetAllByNKH]
GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_DeleteByNKH]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonViChiTieu_DeleteByNKH]
GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_CountDonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonViChiTieu_CountDonVi]
GO
/****** Object:  StoredProcedure [dbo].[spDonVi_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonVi_InsertUpdate]
GO
/****** Object:  StoredProcedure [dbo].[spDonVi_InsertNhom]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonVi_InsertNhom]
GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonVi_GetListPaging]
GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetCoBanByNhom]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonVi_GetCoBanByNhom]
GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetByType]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonVi_GetByType]
GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetById]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonVi_GetById]
GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetAllBYNhom]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonVi_GetAllBYNhom]
GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetAll]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spDonVi_GetAll]
GO
/****** Object:  StoredProcedure [dbo].[spComment_Update_GIUANAM_BY_DV]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_Update_GIUANAM_BY_DV]
GO
/****** Object:  StoredProcedure [dbo].[spComment_Update_BY_DV]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_Update_BY_DV]
GO
/****** Object:  StoredProcedure [dbo].[spComment_Update_BY_BAN]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_Update_BY_BAN]
GO
/****** Object:  StoredProcedure [dbo].[spComment_Update_Approve_GiuaNam]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_Update_Approve_GiuaNam]
GO
/****** Object:  StoredProcedure [dbo].[spComment_Update_Approve]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_Update_Approve]
GO
/****** Object:  StoredProcedure [dbo].[spCOMMENT_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spCOMMENT_Insert]
GO
/****** Object:  StoredProcedure [dbo].[spComment_GetGetAll_GIUANAM_BY_DV]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_GetGetAll_GIUANAM_BY_DV]
GO
/****** Object:  StoredProcedure [dbo].[spComment_GetGetAll_BY_DV]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_GetGetAll_BY_DV]
GO
/****** Object:  StoredProcedure [dbo].[spComment_GetGetAll_BY_BAN]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_GetGetAll_BY_BAN]
GO
/****** Object:  StoredProcedure [dbo].[spComment_DELETE_BY_NHOMCT_NHOMDV]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_DELETE_BY_NHOMCT_NHOMDV]
GO
/****** Object:  StoredProcedure [dbo].[spComment_Chot]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_Chot]
GO
/****** Object:  StoredProcedure [dbo].[spComment_CheckChot]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spComment_CheckChot]
GO
/****** Object:  StoredProcedure [dbo].[spChitieu_KHTH_GetChot]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChitieu_KHTH_GetChot]
GO
/****** Object:  StoredProcedure [dbo].[spChitieu_KHTH_CheckDaChotchua]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChitieu_KHTH_CheckDaChotchua]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_InsertUpdate]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_GetListPaging]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GETCAP1]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_GETCAP1]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetByNamKeHoach_MaCap2]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_GetByNamKeHoach_MaCap2]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetByNamKeHoach_DONVI]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_GetByNamKeHoach_DONVI]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetByNamKeHoach]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_GetByNamKeHoach]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetByMaCap2_Cap]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_GetByMaCap2_Cap]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetByMaCap1_Cap]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_GetByMaCap1_Cap]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetById]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_GetById]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetAll_Chi_Tieu_Rieng]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_GetAll_Chi_Tieu_Rieng]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetAll]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_GetAll]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_Delete]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_Delete]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_Chi_Tieu_Rieng_BY_DV]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_Chi_Tieu_Rieng_BY_DV]
GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_Check_Type]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[spChiTieu_Check_Type]
GO
/****** Object:  StoredProcedure [dbo].[pNamKeHoach_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[pNamKeHoach_InsertUpdate]
GO
/****** Object:  StoredProcedure [dbo].[pNamKeHoach_GetGetAll]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[pNamKeHoach_GetGetAll]
GO
/****** Object:  StoredProcedure [dbo].[pNamKeHoach_GetByNam]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[pNamKeHoach_GetByNam]
GO
/****** Object:  StoredProcedure [dbo].[pNamKeHoach_GetByID]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[pNamKeHoach_GetByID]
GO
/****** Object:  StoredProcedure [dbo].[pNamKeHoach_DELETE]    Script Date: 24/05/2016 12:49:00 AM ******/
DROP PROCEDURE [dbo].[pNamKeHoach_DELETE]
GO
/****** Object:  StoredProcedure [dbo].[pNamKeHoach_DELETE]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[pNamKeHoach_DELETE]
	@NAM_KE_HOACH_ID int

AS
BEGIN
	DELETE  FROM [NV].[NAMKEHOACH]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
END





GO
/****** Object:  StoredProcedure [dbo].[pNamKeHoach_GetByID]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[pNamKeHoach_GetByID]
	@NAM_KE_HOACH_ID int

AS
BEGIN
	SELECT * FROM [NV].[NAMKEHOACH]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
END





GO
/****** Object:  StoredProcedure [dbo].[pNamKeHoach_GetByNam]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[pNamKeHoach_GetByNam]
	@NAM_KE_HOACH int

AS
BEGIN
	SELECT * FROM [NV].[NAMKEHOACH]
	WHERE [NAM_KE_HOACH] = @NAM_KE_HOACH
END





GO
/****** Object:  StoredProcedure [dbo].[pNamKeHoach_GetGetAll]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[pNamKeHoach_GetGetAll]

AS
BEGIN
	SELECT * FROM [NV].[NAMKEHOACH]
END





GO
/****** Object:  StoredProcedure [dbo].[pNamKeHoach_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[pNamKeHoach_InsertUpdate]
	@NAM_KE_HOACH_ID int
	,@NAM_KE_HOACH int
	,@STATUS_ID int
	,@STATUS_DATE  date
AS
BEGIN
	IF(@NAM_KE_HOACH_ID = 0)
		BEGIN
		IF EXISTS(SELECT * FROM [NV].[NAMKEHOACH] WHERE [NAM_KE_HOACH] =  @NAM_KE_HOACH )
		BEGIN
		SELECT 0
		END
		ELSE
		BEGIN
		INSERT INTO [NV].[NAMKEHOACH]
			   ([NAM_KE_HOACH]
			,[STATUS_ID]
			,[STATUS_DATE])
			VALUES
			   (
			   @NAM_KE_HOACH,
			   @STATUS_ID,
			   @STATUS_DATE
			   )
			   Select IDENT_CURRENT('[NV].[NAMKEHOACH]')
		END
			
		END
	ELSE 
		BEGIN
			UPDATE [NV].[NAMKEHOACH] SET
				[NAM_KE_HOACH]=@NAM_KE_HOACH,
				[STATUS_ID]=@STATUS_ID,
				[STATUS_DATE]=@STATUS_DATE
			WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
		END
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_Check_Type]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChiTieu_Check_Type]
	@MA_CAP1 int
AS
BEGIN
		SELECT *		
		FROM [DM].[CHITIEU]
		WHERE  CAP=1 and [MA_CAP1] = @MA_CAP1
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_Chi_Tieu_Rieng_BY_DV]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChiTieu_Chi_Tieu_Rieng_BY_DV]
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int
AS
BEGIN
		SELECT dv.* 
		FROM [NV].[DONVI_CHITIEU] dv
		join [DM].[CHITIEU] ct on ct.CHI_TIEU_ID = dv.CHI_TIEU_ID 
		WHERE dv.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID and dv.DON_VI_ID = @DON_VI_ID and ct.[TYPE] =2 
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_Delete]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChiTieu_Delete]
	@CHI_TIEU_ID int
AS
BEGIN
	UPDATE [DM].[CHITIEU]
	SET [DELETED]= 1
	WHERE [CHI_TIEU_ID] = @CHI_TIEU_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetAll]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChiTieu_GetAll]
	
AS
BEGIN
		SELECT 
			[CHI_TIEU_ID]
			,[MA]
			,[CAP]
			,[MA_CAP1]
			,[MA_CAP2]
			,[MA_CAP3]
			,[TEN_CHI_TIEU]
			,[TEN_CHI_TIEU_CAP1]
			,[TEN_CHI_TIEU_CAP2]
			,[TEN_CHI_TIEU_CAP3]
			,[GHI_CHU]
			,[DELETED]
			,[START_DATE]
			,[END_DATE]
			,[LAST_UPDATED]
			,[TYPE]		
		FROM [DM].[CHITIEU]
		WHERE  [TYPE] = 1
		ORDER BY MA
		
		
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetAll_Chi_Tieu_Rieng]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChiTieu_GetAll_Chi_Tieu_Rieng]
	@DON_VI_ID int 
AS
BEGIN
		SELECT 
			[CHI_TIEU_ID]
		  ,[MA]
		  ,[CAP]
		  ,[MA_CAP1]
		  ,[MA_CAP2]
		  ,[MA_CAP3]
		  ,[TEN_CHI_TIEU]
		  ,[TEN_CHI_TIEU_CAP1]
		  ,[TEN_CHI_TIEU_CAP2]
		  ,[TEN_CHI_TIEU_CAP3]
		  ,[GHI_CHU]
		  ,[DELETED]
		  ,[START_DATE]
		  ,[END_DATE]
		  ,[LAST_UPDATED]
		  ,[TYPE]
		  ,[DON_VI_TINH]
		  ,[DON_VI_ID]
		FROM [DM].[CHITIEU]
		WHERE  [TYPE] = 2 and [DON_VI_ID] = @DON_VI_ID
		ORDER BY MA
		
		
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetById]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChiTieu_GetById]
	@CHI_TIEU_ID int
AS
BEGIN
		SELECT 
			[CHI_TIEU_ID]
			,[MA]
			,[CAP]
			,[MA_CAP1]
			,[MA_CAP2]
			,[MA_CAP3]
			,[TEN_CHI_TIEU]
			,[TEN_CHI_TIEU_CAP1]
			,[TEN_CHI_TIEU_CAP2]
			,[TEN_CHI_TIEU_CAP3]
			,[GHI_CHU]
			,[DELETED]
			,[START_DATE]
			,[END_DATE]
			,[LAST_UPDATED]
			,[TYPE]		
		FROM [HTTTKH_DHQG].[DM].[CHITIEU]
		WHERE [CHI_TIEU_ID] = @CHI_TIEU_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetByMaCap1_Cap]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChiTieu_GetByMaCap1_Cap]
	@MA_CAP1 varchar(2),
	@CAP int
AS
BEGIN
		SELECT 
			[CHI_TIEU_ID]
			,[MA]
			,[CAP]
			,[MA_CAP1]
			,[MA_CAP2]
			,[MA_CAP3]
			,[TEN_CHI_TIEU]
			,[TEN_CHI_TIEU_CAP1]
			,[TEN_CHI_TIEU_CAP2]
			,[TEN_CHI_TIEU_CAP3]
			,[GHI_CHU]
			,[DELETED]
			,[START_DATE]
			,[END_DATE]
			,[LAST_UPDATED]
			,[TYPE]		
		FROM [HTTTKH_DHQG].[DM].[CHITIEU]
		WHERE [MA_CAP1] = @MA_CAP1 and [CAP] = @CAP
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetByMaCap2_Cap]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[spChiTieu_GetByMaCap2_Cap]
	@MA_CAP2 varchar(3),
	@CAP int
AS
BEGIN
		SELECT 
			[CHI_TIEU_ID]
			,[MA]
			,[CAP]
			,[MA_CAP1]
			,[MA_CAP2]
			,[MA_CAP3]
			,[TEN_CHI_TIEU]
			,[TEN_CHI_TIEU_CAP1]
			,[TEN_CHI_TIEU_CAP2]
			,[TEN_CHI_TIEU_CAP3]
			,[GHI_CHU]
			,[DELETED]
			,[START_DATE]
			,[END_DATE]
			,[LAST_UPDATED]
			,[TYPE]		
		FROM [HTTTKH_DHQG].[DM].[CHITIEU]
		WHERE [MA_CAP2] = @MA_CAP2 and [CAP] = @CAP
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetByNamKeHoach]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
-- @DON_VI_ID = 0: loc theo nam ke hoach
-- @DON_VI_ID > 0 : loc theo nam ke hoach va don vi
CREATE PROCEDURE [dbo].[spChiTieu_GetByNamKeHoach]
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int
AS
BEGIN
	if(@DON_VI_ID > 0)
		BEGIN
			with t1 as(
				SELECT ct.*
				FROM [NV].[NAMKH_CHITIEU] nc join [DM].[CHITIEU] ct 
				ON nc.CHI_TIEU_ID = ct.CHI_TIEU_ID join NV.DONVI_CHITIEU dvct
				ON ct.CHI_TIEU_ID = dvct.CHI_TIEU_ID and ct.[TYPE] = 1
				WHERE dvct.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID AND dvct.FLAG=1 and
					 (@DON_VI_ID = 0 OR dvct.DON_VI_ID = @DON_VI_ID)
			)
		
				SELECT * FROM [DM].[CHITIEU] ct 
				WHERE (MA_CAP1 in (select distinct MA_CAP1 from t1) and CAP = 1) OR  
					  (
						MA_CAP1 in (select distinct MA_CAP1 from t1 ) and 
						MA_CAP2 in (select distinct MA_CAP2 from t1 ) and CAP = 2
					   ) 
				UNION
				SELECT * FROM t1
				ORDER BY ct.MA
		END
	else 
		BEGIN
			with t1 as(
				SELECT ct.*
				FROM [NV].[NAMKH_CHITIEU] nc join [DM].[CHITIEU] ct 
				ON nc.CHI_TIEU_ID = ct.CHI_TIEU_ID 
				WHERE nc.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID and ct.[TYPE] = 1
			)
		
				SELECT * FROM [DM].[CHITIEU] ct 
				WHERE (MA_CAP1 in (select distinct MA_CAP1 from t1) and CAP = 1) OR  
					  (
						MA_CAP1 in (select distinct MA_CAP1 from t1 ) and 
						MA_CAP2 in (select distinct MA_CAP2 from t1 ) and CAP = 2
					   ) 
				UNION
				SELECT * FROM t1
				ORDER BY ct.MA
		END
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetByNamKeHoach_DONVI]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
-- @DON_VI_ID = 0: loc theo nam ke hoach
-- @DON_VI_ID > 0 : loc theo nam ke hoach va don vi
CREATE PROCEDURE [dbo].[spChiTieu_GetByNamKeHoach_DONVI]
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int
AS
BEGIN
	if(@DON_VI_ID > 0)
		BEGIN
			with t1 as(
				SELECT ct.*
				FROM NV.DONVI_CHITIEU nc join [DM].[CHITIEU] ct 
				ON nc.CHI_TIEU_ID = ct.CHI_TIEU_ID
				WHERE nc.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID AND nc.FLAG = 0 and
					 nc.DON_VI_ID = @DON_VI_ID
			)
		
				SELECT * FROM [DM].[CHITIEU] ct 
				WHERE (MA_CAP1 in (select distinct MA_CAP1 from t1) and CAP = 1) OR  
					  (
						MA_CAP1 in (select distinct MA_CAP1 from t1 ) and 
						MA_CAP2 in (select distinct MA_CAP2 from t1 ) and CAP = 2
					   ) 
				UNION
				SELECT * FROM t1
				ORDER BY ct.MA
		END
	else 
		BEGIN
			with t1 as(
				SELECT ct.*
				FROM [NV].[NAMKH_CHITIEU] nc join [DM].[CHITIEU] ct 
				ON nc.CHI_TIEU_ID = ct.CHI_TIEU_ID 
				WHERE nc.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID 
			)
		
				SELECT * FROM [DM].[CHITIEU] ct 
				WHERE (MA_CAP1 in (select distinct MA_CAP1 from t1) and CAP = 1) OR  
					  (
						MA_CAP1 in (select distinct MA_CAP1 from t1 ) and 
						MA_CAP2 in (select distinct MA_CAP2 from t1 ) and CAP = 2
					   ) 
				UNION
				SELECT * FROM t1
				ORDER BY ct.MA
		END
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetByNamKeHoach_MaCap2]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChiTieu_GetByNamKeHoach_MaCap2]
	@NAM_KE_HOACH_ID int,
	@MA_CAP2 nvarchar(3)
AS
BEGIN
	SELECT ct.*
		FROM [NV].[NAMKH_CHITIEU] nc join [DM].[CHITIEU] ct 
		ON nc.CHI_TIEU_ID = ct.CHI_TIEU_ID 
		WHERE nc.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID AND
			  ct.MA_CAP2 = @MA_CAP2
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GETCAP1]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChiTieu_GETCAP1]
	@NAM_KE_HOACH_ID int
AS
BEGIN
	SELECT  MA_CAP1, TEN_CHI_TIEU from [DM].[CHITIEU]
	where CAP=1 and MA_CAP1 in (
	select distinct ct.MA_CAP1 from [NV].[NAMKH_CHITIEU] ch
	join [DM].[CHITIEU] ct
	on ch.CHI_TIEU_ID = ct.CHI_TIEU_ID 
	where ch.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID )
	ORDER BY MA_CAP1
END




GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChiTieu_GetListPaging]
	@MA varchar(10),
	@CAP int = 0,
	@TEN_CHI_TIEU nvarchar(255),
	@DELETED int = 2,
	@PAGE_INDEX int,
	@PAGE_SIZE int
AS
BEGIN
	With t1 as(
		SELECT 
			ROW_NUMBER() OVER(ORDER BY CHI_TIEU_ID DESC) AS NUMBER,
			[CHI_TIEU_ID]
			,[MA]
			,[CAP]
			,[MA_CAP1]
			,[MA_CAP2]
			,[MA_CAP3]
			,[TEN_CHI_TIEU]
			,[TEN_CHI_TIEU_CAP1]
			,[TEN_CHI_TIEU_CAP2]
			,[TEN_CHI_TIEU_CAP3]
			,[GHI_CHU]
			,[DELETED]
			,[START_DATE]
			,[END_DATE]
			,[LAST_UPDATED]
			,[TYPE]		
		FROM [HTTTKH_DHQG].[DM].[CHITIEU]
		WHERE 
			(@MA IS NULL OR LOWER(@MA) like LOWER(MA) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI) AND
			(@CAP = 0 OR @CAP = CAP) AND
			(@TEN_CHI_TIEU IS NULL OR LOWER(TEN_CHI_TIEU) like LOWER('%'+@TEN_CHI_TIEU+'%') COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI) AND
			(@DELETED = 2 OR @DELETED = DELETED)
	)

	SELECT t1.*, (SELECT COUNT(*) FROM t1) AS RECORD_COUNT 
	FROM t1
	WHERE NUMBER BETWEEN (@PAGE_INDEX * @PAGE_SIZE + 1) AND
		  ((@PAGE_INDEX + 1) *  @PAGE_SIZE)
END





GO
/****** Object:  StoredProcedure [dbo].[spChiTieu_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[spChiTieu_InsertUpdate]
	@CHI_TIEU_ID int
	,@MA varchar(10)
	,@CAP int
	,@MA_CAP1 varchar(2)
	,@MA_CAP2 varchar(3)
	,@MA_CAP3 varchar(5)
	,@TEN_CHI_TIEU nvarchar(255)
	,@TEN_CHI_TIEU_CAP1 nvarchar(255)
	,@TEN_CHI_TIEU_CAP2 nvarchar(255)
	,@TEN_CHI_TIEU_CAP3 nvarchar(255)
	,@GHI_CHU nvarchar(500)
	--,@DELETED int
	--,@START_DATE 
	--,@END_DATE
	,@LAST_UPDATED datetime
	,@TYPE int
AS
BEGIN
	IF(@CHI_TIEU_ID = 0)
		BEGIN
			INSERT INTO [DM].[CHITIEU]
			   ([MA]
			   ,[CAP]
			   ,[MA_CAP1]
			   ,[MA_CAP2]
			   ,[MA_CAP3]
			   ,[TEN_CHI_TIEU]
			   ,[TEN_CHI_TIEU_CAP1]
			   ,[TEN_CHI_TIEU_CAP2]
			   ,[TEN_CHI_TIEU_CAP3]
			   ,[GHI_CHU]
			   ,[DELETED]
			   --,[START_DATE]
			   --,[END_DATE]
			   ,[LAST_UPDATED]
			   ,[TYPE])
			VALUES
			   (@MA
			   ,@CAP
			   ,@MA_CAP1
			   ,@MA_CAP2
			   ,@MA_CAP3
			   ,@TEN_CHI_TIEU
			   ,@TEN_CHI_TIEU_CAP1
			   ,@TEN_CHI_TIEU_CAP2
			   ,@TEN_CHI_TIEU_CAP3
			   ,@GHI_CHU
			   ,0
			   --,@START_DATE
			   --,@END_DATE
			   ,@LAST_UPDATED
			   ,@TYPE)
		END
	ELSE 
		BEGIN
			UPDATE [DM].[CHITIEU] SET
				[MA] = @MA
			   ,[CAP] = @CAP
			   ,[MA_CAP1] = @MA_CAP1
			   ,[MA_CAP2]= @MA_CAP2
			   ,[MA_CAP3]= @MA_CAP3
			   ,[TEN_CHI_TIEU] =  @TEN_CHI_TIEU
			   ,[TEN_CHI_TIEU_CAP1] =  @TEN_CHI_TIEU_CAP1
			   ,[TEN_CHI_TIEU_CAP2]=  @TEN_CHI_TIEU_CAP2
			   ,[TEN_CHI_TIEU_CAP3]=  @TEN_CHI_TIEU_CAP3
			   ,[GHI_CHU] = @GHI_CHU			   
			   --,[START_DATE] = @START_DATE
			   --,[END_DATE] = @END_DATE
			   ,[LAST_UPDATED]= GETDATE()
			   ,[TYPE] = @TYPE
			WHERE [CHI_TIEU_ID] = @CHI_TIEU_ID
		END
END





GO
/****** Object:  StoredProcedure [dbo].[spChitieu_KHTH_CheckDaChotchua]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChitieu_KHTH_CheckDaChotchua]
	@NAM_KE_HOACH_ID int,
	@MOC_KIEM_SOAT_ID int,
	@DON_VI_ID int
AS
BEGIN
	IF not exists(
		SELECT * FROM [NV].[KEHOACH_THUCHIEN]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID 
		and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
		and [STATUS_ID] = 101
		and IS_CURRENT = 1 
		and [DON_VI_ID] =@DON_VI_ID
	)
	BEGIN
			with t1 as(
				SELECT ct.*
				FROM NV.DONVI_CHITIEU nc join [DM].[CHITIEU] ct 
				ON nc.CHI_TIEU_ID = ct.CHI_TIEU_ID
				WHERE nc.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID AND nc.FLAG = 0 and
					 nc.DON_VI_ID = @DON_VI_ID
			)
		
				SELECT * FROM [DM].[CHITIEU] ct 
				WHERE (MA_CAP1 in (select distinct MA_CAP1 from t1) and CAP = 1) OR  
					  (
						MA_CAP1 in (select distinct MA_CAP1 from t1 ) and 
						MA_CAP2 in (select distinct MA_CAP2 from t1 ) and CAP = 2
					   ) 
				UNION
				SELECT * FROM t1
				ORDER BY MA
	END
	ELSE
	BEGIN
	with t1 as(
				SELECT ct. * FROM [NV].[KEHOACH_THUCHIEN] kt
						join [DM].[CHITIEU] ct on kt.CHI_TIEU_ID = ct.CHI_TIEU_ID
						WHERE kt.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID 
						and kt.[MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
						and kt.[STATUS_ID] = 100
						and kt.IS_CURRENT = 0 
						and kt.[DON_VI_ID] =@DON_VI_ID
			)
		
				SELECT * FROM [DM].[CHITIEU] ct 
				WHERE (MA_CAP1 in (select distinct MA_CAP1 from t1) and CAP = 1) OR  
					  (
						MA_CAP1 in (select distinct MA_CAP1 from t1 ) and 
						MA_CAP2 in (select distinct MA_CAP2 from t1 ) and CAP = 2
					   ) 
				UNION
				SELECT * FROM t1
				ORDER BY MA
	END
END





GO
/****** Object:  StoredProcedure [dbo].[spChitieu_KHTH_GetChot]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spChitieu_KHTH_GetChot]
	@NAM_KE_HOACH_ID int,
	@MOC_KIEM_SOAT_ID int,
	@DON_VI_ID int
AS
BEGIN

	with t1 as(
				SELECT ct. * FROM [NV].[KEHOACH_THUCHIEN] kt
						join [DM].[CHITIEU] ct on kt.CHI_TIEU_ID = ct.CHI_TIEU_ID
						WHERE kt.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID 
						and kt.[MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
						and kt.[STATUS_ID] = 101
						and kt.[DON_VI_ID] =@DON_VI_ID
			)
		
				SELECT * FROM [DM].[CHITIEU] ct 
				WHERE (MA_CAP1 in (select distinct MA_CAP1 from t1) and CAP = 1) OR  
					  (
						MA_CAP1 in (select distinct MA_CAP1 from t1 ) and 
						MA_CAP2 in (select distinct MA_CAP2 from t1 ) and CAP = 2
					   ) 
				UNION
				SELECT * FROM t1
				order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spComment_CheckChot]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_CheckChot]
	@NAM_KE_HOACH_ID int,
	@BAN_ID int,
	@MOC int,
	@MOC_KIEM_SOAT_ID int
AS
BEGIN
	SELECT distinct dv.DON_VI
  FROM [dbo].[COMMENT] cm
  join [DM].[DONVI] dv on cm.[DON_VI_ID] = dv.[DON_VI_ID]
  WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
  and [MOC] =@MOC and [MOC_KIEM_SOAT_ID]=@MOC_KIEM_SOAT_ID  and [STATUS_ID] = 0 and [CHI_TIEU_ID] in (SELECT [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU] where [DON_VI_ID] = @BAN_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID)
END





GO
/****** Object:  StoredProcedure [dbo].[spComment_Chot]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_Chot]
	@NAM_KE_HOACH_ID int,
	@BAN_ID int,
	@MOC_KIEM_SOAT_ID int
AS
BEGIN
	UPDATE [dbo].[COMMENT]
	SET [STATUS_ID] = 3
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [BAN_ID] = @BAN_ID
	UPDATE [NV].[KEHOACH_THUCHIEN]
	SET [IS_CURRENT] = 0
	where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID  and [STATUS_ID] = 101 and [IS_CURRENT] =1 and  [CHI_TIEU_ID] in (SELECT [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU] where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID] = @BAN_ID)
	UPDATE [NV].[KEHOACH_THUCHIEN]
	SET [IS_CURRENT] = 1
  where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID and [STATUS_ID] = 101 and [IS_CURRENT] =0 and  [CHI_TIEU_ID] in (SELECT [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU] where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID] = @BAN_ID)
END





GO
/****** Object:  StoredProcedure [dbo].[spComment_DELETE_BY_NHOMCT_NHOMDV]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_DELETE_BY_NHOMCT_NHOMDV]
	@NHOM_DON_VI_ID int,
	@NHOM_CHI_TIEU int,
	@NAM_KE_HOACH_ID int,
	@BAN_ID int,
	@MOC int,
	@FLAG int
AS
BEGIN
	if(@FLAG = 0)
	DELETE FROM [dbo].[COMMENT]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and [MOC] = @MOC
	and [CHI_TIEU_ID] in (SELECT [CHI_TIEU_ID] from [DM].[CHITIEU] where [CAP] =3 and [TYPE]=1 and [MA_CAP1]=@NHOM_CHI_TIEU)
	and [DON_VI_ID] in (SELECT [DON_VI_ID] from [NV].[DONVI_NHOMDONVI] where [NHOM_DON_VI_ID] = @NHOM_DON_VI_ID)
	and [BAN_ID] = @BAN_ID
	else
	begin
	DELETE FROM [dbo].[COMMENT]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and [MOC] = @MOC
	and [CHI_TIEU_ID] in (SELECT [CHI_TIEU_ID] from [DM].[CHITIEU] where [CAP] =3 and [TYPE]=1 and [MA_CAP1]=@NHOM_CHI_TIEU)
	and [DON_VI_ID] = @NHOM_DON_VI_ID
	and [BAN_ID] = @BAN_ID
	end

END





GO
/****** Object:  StoredProcedure [dbo].[spComment_GetGetAll_BY_BAN]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_GetGetAll_BY_BAN]
	@BAN_ID int,
	@NAM_KE_HOACH_ID int,
	@MOC int,
	@FLAG int,
	@MOC_KIEM_SOAT_ID int
AS
BEGIN
	if(@FLAG =0)
	begin
	SELECT * FROM [dbo].[COMMENT]
	WHERE [BAN_ID] = @BAN_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and @MOC=[MOC] and [IS_ACTIVE] =1 and [MOC_KIEM_SOAT_ID]=@MOC_KIEM_SOAT_ID
	ORDER BY [STATUS_ID]
	end
	if(@FLAG =3)
	begin
	SELECT * FROM [dbo].[COMMENT]
	WHERE [BAN_ID] = @BAN_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and @MOC=[MOC] and [IS_ACTIVE] =1 and [STATUS_ID] =1 and [MOC_KIEM_SOAT_ID]=@MOC_KIEM_SOAT_ID
	ORDER BY [STATUS_ID]
	end
	if(@FLAG =2)
	begin
	SELECT * FROM [dbo].[COMMENT]
	WHERE [BAN_ID] = @BAN_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and @MOC=[MOC] and [IS_ACTIVE] =1 and [IS_ACTIVE] =1
	ORDER BY [STATUS_ID]
	end
END





GO
/****** Object:  StoredProcedure [dbo].[spComment_GetGetAll_BY_DV]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_GetGetAll_BY_DV]
	@DON_VI_ID int,
	@NAM_KE_HOACH_ID int,
	@MOC int,
	@FLAG int,
	@MOC_KIEM_SOAT_ID int
AS
BEGIN
	if(@FLAG = 1)
	begin
	SELECT 
		cm.[COMMENT_ID]
      ,cm.[BAN_ID]
	  ,kh.[GIA_TRI] as KeHoachDauNam
	  ,kh1.[GIA_TRI] as DieuChinh
	  ,dv.[DON_VI]
	  ,kt.[GIA_TRI] as ThucHien
      ,cm.[CHI_TIEU_ID]
	  ,ct.[TEN_CHI_TIEU]
	  ,ct.[DON_VI_TINH]
	  ,cm.[COMMENT_DV]
      ,cm.[COMMENT_BAN]
      ,cm.[DON_VI_ID]
	  ,dv1.DON_VI as TenDV
  FROM [dbo].[COMMENT]  cm
  left join [DM].[CHITIEU] ct on cm.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID]
  join [DM].[DONVI] dv on cm.[BAN_ID] = dv. [DON_VI_ID]
  join [DM].[DONVI] dv1 on cm.[DON_VI_ID] = dv1. [DON_VI_ID]
  left join [NV].[KEHOACH] kh on cm.[DON_VI_ID] = kh.[DON_VI_ID] and kh.[STATUS_ID] = 31 and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and cm.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID]
  left join [NV].[KEHOACH] kh1 on cm.[DON_VI_ID] = kh1.[DON_VI_ID] and kh1.[STATUS_ID] = 61 and kh1.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and cm.[CHI_TIEU_ID] = kh1.[CHI_TIEU_ID]
  left join [NV].[KEHOACH_THUCHIEN] kt on cm.[DON_VI_ID] = kt.[DON_VI_ID] and kt.[IS_CURRENT] = 0 and kt.[STATUS_ID]=101 and kt.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and cm.[CHI_TIEU_ID] = kt.[CHI_TIEU_ID]
  WHERE cm.[DON_VI_ID] = @DON_VI_ID and cm.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and @MOC=cm.[MOC] and cm.[IS_ACTIVE] =1 and cm.[STATUS_ID] = 0 and cm.[MOC_KIEM_SOAT_ID]=@MOC_KIEM_SOAT_ID
  ORDER BY cm.[DON_VI_ID]
  end
  if(@FLAG = 0)
  begin
  SELECT 
		cm.[COMMENT_ID]
      ,cm.[BAN_ID]
	  ,kh.[GIA_TRI] as KeHoachDauNam
	  ,kh1.[GIA_TRI] as DieuChinh
	  ,dv.[DON_VI]
	  ,kt.[GIA_TRI] as ThucHien
      ,cm.[CHI_TIEU_ID]
	  ,ct.[TEN_CHI_TIEU]
	  ,ct.[DON_VI_TINH]
      ,cm.[COMMENT_BAN]
	  ,cm.[COMMENT_DV]
      ,cm.[DON_VI_ID]
	  ,dv1.DON_VI as TenDV
  FROM [dbo].[COMMENT]  cm
  left join [DM].[CHITIEU] ct on cm.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID]
  join [DM].[DONVI] dv on cm.[BAN_ID] = dv. [DON_VI_ID]
  join [DM].[DONVI] dv1 on cm.[DON_VI_ID] = dv1. [DON_VI_ID]
  left join [NV].[KEHOACH] kh on cm.[DON_VI_ID] = kh.[DON_VI_ID] and kh.[STATUS_ID] = 31 and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and cm.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID]
  left join [NV].[KEHOACH] kh1 on cm.[DON_VI_ID] = kh1.[DON_VI_ID] and kh1.[STATUS_ID] = 61 and kh1.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and cm.[CHI_TIEU_ID] = kh1.[CHI_TIEU_ID]
  left join [NV].[KEHOACH_THUCHIEN] kt on cm.[DON_VI_ID] = kt.[DON_VI_ID] and kt.[IS_CURRENT] = 0 and kt.[STATUS_ID]=101 and kt.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and cm.[CHI_TIEU_ID] = kt.[CHI_TIEU_ID]
  WHERE cm.[BAN_ID] = @DON_VI_ID and cm.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and @MOC=cm.[MOC] and cm.[IS_ACTIVE] =1 and cm.[STATUS_ID] = 1 and cm.[MOC_KIEM_SOAT_ID]=@MOC_KIEM_SOAT_ID
  ORDER BY cm.[DON_VI_ID]
  end
  if(@FLAG = 2)
  begin
  SELECT distinct
		cm.[COMMENT_ID]
      ,cm.[BAN_ID]
	  ,kh.[GIA_TRI] as KeHoachDauNam
	  ,dv.[DON_VI]
	  ,cm.[GIA_TRI] as ThucHien
      ,cm.[CHI_TIEU_ID]
	  ,ct.[TEN_CHI_TIEU]
	  ,ct.[DON_VI_TINH]
      ,cm.[COMMENT_BAN]
	  ,cm.[COMMENT_DV]
      ,cm.[DON_VI_ID]
	  ,dv1.DON_VI as TenDV
  FROM [dbo].[COMMENT]  cm
  join [DM].[CHITIEU] ct on cm.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID]
  join [DM].[DONVI] dv on dv. [DON_VI_ID] = @DON_VI_ID
  join [DM].[DONVI] dv1 on cm.[DON_VI_ID] = dv1. [DON_VI_ID]
  join [NV].[KEHOACH] kh on cm.[DON_VI_ID] = kh.[DON_VI_ID] and kh.[STATUS_ID] = 31 and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and cm.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID]
  join [NV].[DONVI_CHITIEU] dvct on dvct.[CHI_TIEU_ID] = cm.[CHI_TIEU_ID] and dvct.[DON_VI_ID] = @DON_VI_ID and dvct.FLAG=1
  WHERE cm.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and @MOC=cm.[MOC] and cm.[IS_ACTIVE] =1 and cm.[STATUS_ID] = 1 
  ORDER BY cm.[DON_VI_ID]
  end
  if(@FLAG = 3)
  begin
  SELECT distinct
		cm.[COMMENT_ID]
      ,cm.[BAN_ID]
	  ,kh.[GIA_TRI] as KeHoachDauNam
	  ,dv.[DON_VI]
	  ,cm.[GIA_TRI] as ThucHien
      ,cm.[CHI_TIEU_ID]
	  ,ct.[TEN_CHI_TIEU]
	  ,ct.[DON_VI_TINH]
      ,cm.[COMMENT_BAN]
	  ,cm.[COMMENT_DV]
      ,cm.[DON_VI_ID]
	  ,dv1.DON_VI as TenDV
  FROM [dbo].[COMMENT]  cm
  join [DM].[CHITIEU] ct on cm.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID]
  join [DM].[DONVI] dv on dv. [DON_VI_ID] = @DON_VI_ID
  join [DM].[DONVI] dv1 on cm.[DON_VI_ID] = dv1. [DON_VI_ID]
  join [NV].[KEHOACH] kh on cm.[DON_VI_ID] = kh.[DON_VI_ID] and kh.[STATUS_ID] = 31 and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and cm.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID]
  join [NV].[DONVI_CHITIEU] dvct on dvct.[CHI_TIEU_ID] = cm.[CHI_TIEU_ID] and dvct.[DON_VI_ID] = @DON_VI_ID and dvct.FLAG=1
  WHERE cm.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and @MOC=cm.[MOC] and cm.[IS_ACTIVE] =1 and cm.[STATUS_ID] = 3 
  ORDER BY cm.[DON_VI_ID]
  end
  if(@FLAG = 4)
  begin
  SELECT distinct
		cm.[COMMENT_ID]
      ,cm.[BAN_ID]
	  ,kh.[GIA_TRI] as KeHoachDauNam
	  ,dv.[DON_VI]
	  ,cm.[GIA_TRI] as ThucHien
      ,cm.[CHI_TIEU_ID]
	  ,ct.[TEN_CHI_TIEU]
	  ,ct.[DON_VI_TINH]
      ,cm.[COMMENT_BAN]
	  ,cm.[COMMENT_DV]
      ,cm.[DON_VI_ID]
	  ,dv1.DON_VI as TenDV
  FROM [dbo].[COMMENT]  cm
  join [DM].[CHITIEU] ct on cm.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID]
   join [DM].[DONVI] dv on dv. [DON_VI_ID] = cm.BAN_ID
  join [DM].[DONVI] dv1 on cm.[DON_VI_ID] = dv1. [DON_VI_ID]
  join [NV].[KEHOACH] kh on cm.[DON_VI_ID] = kh.[DON_VI_ID] and kh.[STATUS_ID] = 31 and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and cm.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID]
  WHERE cm.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and @MOC=cm.[MOC] and cm.[IS_ACTIVE] =1 and cm.[STATUS_ID] = 3 
  ORDER BY cm.[DON_VI_ID]
  end
END





GO
/****** Object:  StoredProcedure [dbo].[spComment_GetGetAll_GIUANAM_BY_DV]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_GetGetAll_GIUANAM_BY_DV]
	@DON_VI_ID int,
	@NAM_KE_HOACH_ID int,
	@MOC int
AS
BEGIN
	SELECT * FROM [dbo].[COMMENT]
	WHERE [DON_VI_ID] = @DON_VI_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and @MOC=[MOC] and [IS_ACTIVE] =1 
	ORDER BY [STATUS_ID]
END





GO
/****** Object:  StoredProcedure [dbo].[spCOMMENT_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spCOMMENT_Insert]
	@Table as [dbo].[COMMENTNEW] readonly
AS
BEGIN
	INSERT INTO [dbo].[COMMENT](
		[BAN_ID]
		,[CHI_TIEU_ID]
		,[COMMENT_BAN]
		,[DON_VI_ID]
		,[GIA_TRI]
		,[COMMENT_DV]
		,[STATUS_ID]
		,[IS_ACTIVE]
		,[MOC]
		,[NAM_KE_HOACH_ID]
		,[MOC_KIEM_SOAT_ID]
	)
	SELECT 
		[BAN_ID]
		,[CHI_TIEU_ID]
		,[COMMENT_BAN]
		,[DON_VI_ID]
		,[GIA_TRI]
		,[COMMENT_DV]
		,[STATUS_ID]
		,[IS_ACTIVE]
		,[MOC]
		,[NAM_KE_HOACH_ID]
		,[MOC_KIEM_SOAT_ID]
	FROM @TABLE
END





GO
/****** Object:  StoredProcedure [dbo].[spComment_Update_Approve]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_Update_Approve]
	@NAM_KE_HOACH_ID int,
	@CHI_TIEU_ID int,
	@DON_VI_ID int,
	@MOC int,
	@MOC_KIEM_SOAT_ID int
AS
BEGIN
	UPDATE [dbo].[COMMENT]
	set
	[STATUS_ID]=3
	where [CHI_TIEU_ID] = @CHI_TIEU_ID and [DON_VI_ID] = @DON_VI_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [MOC]=@MOC  and [MOC_KIEM_SOAT_ID]=@MOC_KIEM_SOAT_ID 

END





GO
/****** Object:  StoredProcedure [dbo].[spComment_Update_Approve_GiuaNam]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_Update_Approve_GiuaNam]
	@NAM_KE_HOACH_ID int,
	@CHI_TIEU_ID int,
	@DON_VI_ID int,
	@MOC int,
	@FLAG int
AS
BEGIN
	if(@FLAG=0)
	begin
	UPDATE [dbo].[COMMENT]
	set
	[STATUS_ID]=3
	where [CHI_TIEU_ID] = @CHI_TIEU_ID and [DON_VI_ID] = @DON_VI_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [MOC]=@MOC 
	end
	if(@FLAG = 1)
	begin
	UPDATE [dbo].[COMMENT]
	set
	[STATUS_ID]=3,
	[BAN_ID] = @CHI_TIEU_ID
	where [DON_VI_ID] = @DON_VI_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [MOC]=@MOC and [CHI_TIEU_ID] in (
	SELECT [CHI_TIEU_ID]
	from [NV].[DONVI_CHITIEU]
	where [DON_VI_ID] = @CHI_TIEU_ID and FLAG=1)
	end
	if(@FLAG = 2)
	begin
	UPDATE [dbo].[COMMENT]
	set
	[STATUS_ID]=3,
	[BAN_ID] = @DON_VI_ID
	where  [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [MOC]=@MOC  and [CHI_TIEU_ID] in (
	SELECT [CHI_TIEU_ID]
	from [NV].[DONVI_CHITIEU]
	where [DON_VI_ID] = @DON_VI_ID and FLAG=1)
	end
END





GO
/****** Object:  StoredProcedure [dbo].[spComment_Update_BY_BAN]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_Update_BY_BAN]
	@NAM_KE_HOACH_ID int,
	@CHI_TIEU_ID int,
	@DON_VI_ID int,
	@COMMENT_BAN nvarchar(500),
	@MOC int,
	@MOC_KIEM_SOAT_ID int
AS
BEGIN
	UPDATE [dbo].[COMMENT]
	set [COMMENT_BAN] = @COMMENT_BAN,
	[STATUS_ID]=0
	where [CHI_TIEU_ID] = @CHI_TIEU_ID and [DON_VI_ID] = @DON_VI_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [MOC]=@MOC  and [MOC_KIEM_SOAT_ID]=@MOC_KIEM_SOAT_ID

END





GO
/****** Object:  StoredProcedure [dbo].[spComment_Update_BY_DV]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_Update_BY_DV]
	@NAM_KE_HOACH_ID int,
	@CHI_TIEU_ID int,
	@DON_VI_ID int,
	@GIA_TRI int,
	@COMMENT_DV nvarchar(500),
	@MOC int,
	@MOC_KIEM_SOAT_ID int
AS
BEGIN
	UPDATE [dbo].[COMMENT]
	set [COMMENT_DV] = @COMMENT_DV,
	[GIA_TRI] = @GIA_TRI,
	[STATUS_ID]=1
	where [CHI_TIEU_ID] = @CHI_TIEU_ID and [DON_VI_ID] = @DON_VI_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [MOC]=@MOC  and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
	UPDATE [NV].[KEHOACH_THUCHIEN]
	set [GIA_TRI] = @GIA_TRI
	where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [CHI_TIEU_ID] = @CHI_TIEU_ID and [DON_VI_ID] = @DON_VI_ID and [IS_CURRENT] =0 and [STATUS_ID] = 101
END





GO
/****** Object:  StoredProcedure [dbo].[spComment_Update_GIUANAM_BY_DV]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spComment_Update_GIUANAM_BY_DV]
	@NAM_KE_HOACH_ID int,
	@CHI_TIEU_ID int,
	@DON_VI_ID int,
	@GIA_TRI int,
	@COMMENT_DV nvarchar(500),
	@MOC int
AS
BEGIN
	UPDATE [dbo].[COMMENT]
	set [COMMENT_DV] = @COMMENT_DV,
	[GIA_TRI] = @GIA_TRI
	where [CHI_TIEU_ID] = @CHI_TIEU_ID and [DON_VI_ID] = @DON_VI_ID and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [MOC]=@MOC  
END





GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetAll]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong	
-- Create date: 20160430
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[spDonVi_GetAll]	
AS
BEGIN
	SELECT [DON_VI_ID]
      ,[DON_VI]
      ,[NHOM_DON_VI_ID]
      ,[GHI_CHU]
      ,[MA_DON_VI]
      ,[CO_QUYEN_GIAM_SAT]
      ,[TEN_VIET_TAT]
  FROM [DM].[DONVI]
END





GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetAllBYNhom]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1
-- Create date: 20160420
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDonVi_GetAllBYNhom]
	@NHOM_DON_VI_ID int
AS
BEGIN
		SELECT  d.DON_VI_ID, d.DON_VI, d.MA_DON_VI, d.CO_QUYEN_GIAM_SAT, d.GHI_CHU, m.NHOM_DON_VI_ID, d.TEN_VIET_TAT
		FROM [DM].[DONVI] d
		JOIN [NV].[DONVI_NHOMDONVI] m
		on d.DON_VI_ID = m.DON_VI_ID
		WHERE m.[NHOM_DON_VI_ID] = @NHOM_DON_VI_ID
		
END





GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetById]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1	
-- Create date: 20160413
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDonVi_GetById]
	@DON_VI_ID int
AS
BEGIN
	SELECT 
			n.*
		FROM [DM].[DONVI] n
		WHERE [DON_VI_ID] = @DON_VI_ID
  
END





GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetByType]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1
-- Create date: 20160409
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDonVi_GetByType]
	@CO_QUYEN_GIAM_SAT bit
AS
BEGIN
		--SELECT  *
		--FROM [DM].[DONVI]
		--WHERE [CO_QUYEN_GIAM_SAT] = @CO_QUYEN_GIAM_SAT

		SELECT *
		FROM [DM].[DONVI]
		WHERE 
			(@CO_QUYEN_GIAM_SAT = 1 AND ([CO_QUYEN_GIAM_SAT] = 1  OR [CO_QUYEN_GIAM_SAT] = 2))
			OR
			(@CO_QUYEN_GIAM_SAT = 0 AND ([CO_QUYEN_GIAM_SAT] = 0  OR [CO_QUYEN_GIAM_SAT] = 2))
			

		
END





GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetCoBanByNhom]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1
-- Create date: 20160420
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDonVi_GetCoBanByNhom]
	@NHOM_DON_VI_ID int
AS
BEGIN
		SELECT  d.DON_VI_ID, d.DON_VI, d.MA_DON_VI, d.CO_QUYEN_GIAM_SAT, d.GHI_CHU, m.NHOM_DON_VI_ID, d.TEN_VIET_TAT
		FROM [DM].[DONVI] d
		JOIN [NV].[DONVI_NHOMDONVI] m
		on d.DON_VI_ID = m.DON_VI_ID
		WHERE m.[NHOM_DON_VI_ID] = @NHOM_DON_VI_ID
		and m.IS_CO_BAN = 1
		
END





GO
/****** Object:  StoredProcedure [dbo].[spDonVi_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1	
-- Create date: 20160409
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDonVi_GetListPaging]
	@MA_DON_VI nvarchar(50),
	@TEN_DON_VI nvarchar(255),
	@NHOM_DON_VI_ID int,
	@CO_QUYEN_GIAM_SAT int,
	--@BOOL_CO_QUYEN_GIAM_SAT bit,
	@PAGE_INDEX INT,
	@PAGE_SIZE INT
AS
BEGIN
	with t1 as
	(
		SELECT 
			ROW_NUMBER() OVER(ORDER BY d.[DON_VI_ID] desc) AS NUMBER
			,d.[DON_VI_ID]
			,d.[NHOM_DON_VI_ID]
			--,n.TEN_NHOM_DON_VI
			,d.[MA_DON_VI]
			,d.[DON_VI]
			,d.[GHI_CHU]
			,d.[CO_QUYEN_GIAM_SAT] 
			,d.TEN_VIET_TAT
		FROM [DM].[DONVI] d
		LEFT JOIN [NV].[DONVI_NHOMDONVI] m
		on d.DON_VI_ID = m.DON_VI_ID and m.IS_CO_BAN = 1
		LEFT JOIN [DM].[NHOMDONVI] n
		on m.NHOM_DON_VI_ID = n.NHOM_DON_VI_ID and m.IS_CO_BAN = 1
		WHERE (@TEN_DON_VI is null OR 
			  LOWER(d.[DON_VI]) LIKE LOWER(@TEN_DON_VI)
			  COLLATE SQL_Latin1_General_Cp1_CI_AI) -- khong phan biet co dau khong dau
			  AND 
			  (@NHOM_DON_VI_ID = 0 or m.NHOM_DON_VI_ID = @NHOM_DON_VI_ID)
			  AND
			  (@MA_DON_VI is null or d.MA_DON_VI = @MA_DON_VI)
			  AND
			  (d.CO_QUYEN_GIAM_SAT = @CO_QUYEN_GIAM_SAT OR @CO_QUYEN_GIAM_SAT = 3)
  )

  SELECT t1.*
		, (SELECT COUNT(*) FROM t1 ) AS RECORD_COUNT
		,STUFF((SELECT ', ' + nhom.TEN_NHOM_DON_VI AS [text()]
                         FROM [NV].[DONVI_NHOMDONVI] m
						 JOIN [DM].NHOMDONVI nhom
						 on m.NHOM_DON_VI_ID = nhom.NHOM_DON_VI_ID
                         WHERE m.DON_VI_ID = t1.DON_VI_ID
                         FOR XML PATH(''))
                     , 1, 1, '' ) AS TEN_NHOM_DON_VI
  FROM t1
  WHERE NUMBER BETWEEN (@PAGE_INDEX * @PAGE_SIZE + 1) AND 
					   (@PAGE_INDEX + 1) * @PAGE_SIZE
  

END




GO
/****** Object:  StoredProcedure [dbo].[spDonVi_InsertNhom]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[spDonVi_InsertNhom]
	@NHOM_DON_VI_ID int,
	@DON_VI_ID int,
	@LIST_NHOMID dbo.NhomIDTableTemp READONLY
AS
BEGIN
	DELETE FROM [NV].[DONVI_NHOMDONVI] where [DON_VI_ID] = @DON_VI_ID
	--insert nhom co ban
	INSERT INTO [NV].[DONVI_NHOMDONVI] 
	(
		[DON_VI_ID]
		,[NHOM_DON_VI_ID]
		,[IS_CO_BAN]
	)
	VALUES 
	(
		@DON_VI_ID
		,@NHOM_DON_VI_ID
		,1
	)

	--insert nhom khac
	INSERT INTO [NV].[DONVI_NHOMDONVI] 
	(
		[DON_VI_ID]
		,[NHOM_DON_VI_ID]
		,[IS_CO_BAN]
	)
	SELECT 
		@DON_VI_ID
		,[NHOM_DON_VI_ID]
		,0
	FROM @LIST_NHOMID


END





GO
/****** Object:  StoredProcedure [dbo].[spDonVi_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1	
-- Create date: 20160413
-- udpate: 20160511
-- Description:	thay doi truong CO_QUYEN_GIAM_SAT thanh int
-- =============================================
CREATE PROCEDURE [dbo].[spDonVi_InsertUpdate]
	@DON_VI_ID int,
	--@NHOM_DON_VI_ID int,
	@TEN_DON_VI nvarchar(255),
	@MA_DON_VI nvarchar(255),
	@CO_QUYEN_GIAM_SAT int,
	@GHI_CHU nvarchar(255),
	@TEN_VIET_TAT varchar(10)
AS
BEGIN
	if(@DON_VI_ID <= 0)
		BEGIN
			INSERT INTO [DM].[DONVI]
				   ([DON_VI]
					--,[NHOM_DON_VI_ID]
					,[MA_DON_VI]
					,[CO_QUYEN_GIAM_SAT]
					,[GHI_CHU]
					,[TEN_VIET_TAT])
			 VALUES
				   (@TEN_DON_VI
				   --,@NHOM_DON_VI_ID
				   ,@MA_DON_VI
				   ,@CO_QUYEN_GIAM_SAT
				   ,@GHI_CHU
				   ,@TEN_VIET_TAT)
		END
	else
		BEGIN
			UPDATE [DM].[DONVI] 
			SET [DON_VI] = @TEN_DON_VI,
			--[NHOM_DON_VI_ID] = @NHOM_DON_VI_ID,
			[MA_DON_VI] = @MA_DON_VI,
			[CO_QUYEN_GIAM_SAT] = @CO_QUYEN_GIAM_SAT,
			[GHI_CHU] = @GHI_CHU,
			[TEN_VIET_TAT]=@TEN_VIET_TAT
			where DON_VI_ID = @DON_VI_ID
		END
END





GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_CountDonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lấy ra số lượng đơn vị sử dụng chỉ tiêu nhất định
-- =============================================
CREATE PROCEDURE [dbo].[spDonViChiTieu_CountDonVi]
	@NAM_KE_HOACH_ID int,
	@CHI_TIEU_ID int	
AS
BEGIN
	 SELECT COUNT(*)
	 FROM [NV].[DONVI_CHITIEU]
	 WHERE CHI_TIEU_ID = @CHI_TIEU_ID  and 
		   [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and  
		   FLAG= 0
END

GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_DeleteByNKH]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  huennb
-- Create date: 20160402
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDonViChiTieu_DeleteByNKH]
 @NAM_KE_HOACH_ID int,
 @DON_VI_ID int,
 @FLAG int

AS
BEGIN
 delete from [NV].[DONVI_CHITIEU]
 WHERE  [NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID and DON_VI_ID= @DON_VI_ID and FLAG= @FLAG
END




GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_GetAllByNKH]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDonViChiTieu_GetAllByNKH]
	@NAM_KE_HOACH_ID int,
	@FLAG int

AS
BEGIN
	select * from [NV].[DONVI_CHITIEU]
	Left join [DM].[DONVI]
	ON [NV].[DONVI_CHITIEU].DON_VI_ID = [DM].[DONVI].DON_VI_ID
	WHERE  [NV].[DONVI_CHITIEU].NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID and [NV].[DONVI_CHITIEU].FLAG = @FLAG
END





GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_GetByDonvi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1	
-- Create date: 20160413
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDonViChiTieu_GetByDonvi]
	@DON_VI_ID int,
	@NAM_KE_HOACH_ID int
AS
BEGIN
		SELECT dc.*
		FROM [NV].[DONVI_CHITIEU] dc
		join [DM].[CHITIEU] ct on ct.CHI_TIEU_ID =dc.CHI_TIEU_ID 
		WHERE dc.[DON_VI_ID] = @DON_VI_ID and dc.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID and ct.[TYPE] = 1 and dc.FLAG = 0

  
END





GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDonViChiTieu_Insert]
	@Table as [dbo].[DV_CT_NEW] readonly
AS
BEGIN
	INSERT INTO [NV].[DONVI_CHITIEU]([DON_VI_ID],[CHI_TIEU_ID],[NAM_KE_HOACH_ID],[FLAG])
	SELECT [DON_VI_ID],[CHI_TIEU_ID],[NAM_KE_HOACH_ID] ,[FLAG] FROM @Table
END





GO
/****** Object:  StoredProcedure [dbo].[spDonViChiTieu_Rieng_DeleteByNKH]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spDonViChiTieu_Rieng_DeleteByNKH]
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int
AS
BEGIN
	delete from [NV].[DONVI_CHITIEU] 
	WHERE  [DON_VI_CHI_TIEU_ID] in (SELECT [DON_VI_CHI_TIEU_ID]
									FROM [NV].[DONVI_CHITIEU] dc
									join [DM].[CHITIEU] ct 
									on dc.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID]
									where ct.[TYPE] = 2 and dc.[DON_VI_ID] = @DON_VI_ID and dc.[NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID and dc.FLAG=0)
	
	end




GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_Ban_Phu_Trach]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra chi tieu cua don vi, bao gom toan bo cac chi tieu,
-- cac chi tieu khong thuoc don co gia tri = 0
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_Ban_Phu_Trach]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		--TEN_VIET_TAT nvarchar(50),
		DON_VI nvarchar(100)		
	);

	with t1 as (
		SELECT distinct 0 AS GIA_TRI,
			k.CHI_TIEU_ID 
			,c.TEN_CHI_TIEU		
			,c.MA
			,c.CAP
			,c.MA_CAP1
			,c.MA_CAP2
			,d.TEN_VIET_TAT as DON_VI
			--,d.DON_VI
		FROM [NV].[KEHOACH] k  
		Join [DM].[CHITIEU] c ON k.CHI_TIEU_ID = c.CHI_TIEU_ID
		Join [DM].[DONVI] d ON k.DON_VI_ID = d.DON_VI_ID
		where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = @STATUS_ID  and CAP = 3
	)
	  INSERT INTO @DanhSachChiTieu
	  select * from t1
	  	  
	  select * from(
  		  select  		  
			t1.CHI_TIEU_ID
			bgv,TEN_CHI_TIEU		
			,MA
			,CAP
			,MA_CAP1
			,MA_CAP2		
			--,TEN_VIET_TAT
			,DON_VI
		from @DanhSachChiTieu t1 		  
		union
		select distinct
				c.CHI_TIEU_ID
				,c.TEN_CHI_TIEU
				,c.MA
				,c.CAP		
				,c.MA_CAP1
				,c.MA_CAP2
				,NULL
							  
		from [DM].[CHITIEU] c
		where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				(c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results 
	  Order by MA  
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_Ban_Phu_Trach_TheoDonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra chi tieu cua don vi, bao gom toan bo cac chi tieu,
-- cac chi tieu khong thuoc don co gia tri = 0
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_Ban_Phu_Trach_TheoDonVi]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int=0,
	@DON_VI_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3)		
	);

Declare @DanhSachChiTieu2 Table(		
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3)		
	);

	Declare @DonViPhuTrach Table(
		CHI_TIEU_ID int,
		DON_VI nvarchar(250)
	);

	with t1 as (
		SELECT  distinct 0 AS GIA_TRI,     
			dvct.[CHI_TIEU_ID],
			ct.TEN_CHI_TIEU,
			ct.MA,
			ct.CAP,
			ct.MA_CAP1,
			ct.MA_CAP2
		  FROM [NV].[DONVI_CHITIEU] dvct left join [DM].[CHITIEU] ct
		  ON dvct.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID] 
		  --inner Join [NV].[NAMKH_CHITIEU] kh ON dvct.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID] and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
		  where dvct.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID  and
		  (dvct.DON_VI_ID = @DON_VI_ID)	and ct.TYPE = 1 -- chi tieu chung
	)
	  INSERT INTO @DanhSachChiTieu
	  select * from t1

	  INSERT INTO @DonViPhuTrach
	  select k.[CHI_TIEU_ID], d.[TEN_VIET_TAT] 
	  from [NV].[KEHOACH] k join [DM].[CHITIEU] c
		  on k.[CHI_TIEU_ID] = c.CHI_TIEU_ID join [DM].[DONVI] d 
		  On k.DON_VI_ID = d.DON_VI_ID
	  where k.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and k.status_id = @STATUS_ID and c.CAP = 3	  
	  
	  INSERT INTO @DanhSachChiTieu2
	  select * from(
  		  select  		  
			t1.CHI_TIEU_ID
			bgv,TEN_CHI_TIEU		
			,MA
			,CAP
			,MA_CAP1
			,MA_CAP2			
		  from @DanhSachChiTieu t1 		  
		  union
			select distinct
				  c.CHI_TIEU_ID
				  ,c.TEN_CHI_TIEU
				  ,c.MA
				  ,c.CAP		
				  ,c.MA_CAP1
				  ,c.MA_CAP2	  				  
				from [DM].[CHITIEU] c
				where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
					  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results 
	  Order by MA 

	  select t1.*, k.DON_VI 
	  from @DanhSachChiTieu2 t1 left join @DonViPhuTrach k 
	  ON t1.CHI_TIEU_ID = k.CHI_TIEU_ID	  
	  ORDER BY MA
END


GO
/****** Object:  StoredProcedure [dbo].[spKehoach_Checkchot]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_Checkchot]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@NHOM_CHI_TIEU int

AS
BEGIN
	DECLARE @total int
	SELECT @total=COUNT(*)
	FROM [NV].[DONVI_CHITIEU]
	where [NAM_KE_HOACH_ID]  = @NAM_KE_HOACH_ID and [FLAG] = 0 and
	[DON_VI_ID] in (Select [DON_VI_ID] from [DM].[DONVI] where [CO_QUYEN_GIAM_SAT] = 0 or [CO_QUYEN_GIAM_SAT] = 2)
	and  [CHI_TIEU_ID] in (select [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU] where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID]  = @NHOM_CHI_TIEU and FLAG = 1)
	DECLARE @total_cp int
	SELECT @total_cp= COUNT(*)
	FROM [NV].[KEHOACH]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=@STATUS_ID and 
	[CHI_TIEU_ID] in (select [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU] where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID]  = @NHOM_CHI_TIEU and FLAG = 1)
	IF(@total = @total_cp)
	BEGIN
	UPDATE [NV].[KEHOACH]
	SET STATUS_ID = (@STATUS_ID+1) 
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=@STATUS_ID and 
	[CHI_TIEU_ID] in (select [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU] where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID]  = @NHOM_CHI_TIEU and FLAG = 1)
	END
	ELSE
	BEGIN
	SELECT 1
	END
	IF(@NHOM_CHI_TIEU = 30)
	BEGIN
	UPDATE [NV].[KEHOACH]
	SET STATUS_ID = (@STATUS_ID+1) 
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=@STATUS_ID
	END

END




GO
/****** Object:  StoredProcedure [dbo].[spKehoach_CheckChotByNhomDV]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_CheckChotByNhomDV]
	@NAM_KE_HOACH_ID int,
	@STATUS int,
	@NHOM_CHI_TIEU int

AS
BEGIN
	IF NOT EXISTS(select * from [NV].[KEHOACH] where NAM_KE_HOACH_ID=@NAM_KE_HOACH_ID and STATUS_ID=@STATUS and CHI_TIEU_ID in (select CHI_TIEU_ID from [DM].[CHITIEU] where [MA_CAP2]=@NHOM_CHI_TIEU and CAP=3) )
	BEGIN
	Select 1
	END
	ELSE
	BEGIN
	SELECT 0
	END
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_CheckPhaHoiChot]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_CheckPhaHoiChot]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int

AS
BEGIN
	DECLARE @total int
	SELECT @total= COUNT(*)
	FROM [NV].[DONVI_CHITIEU]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and [DON_VI_ID] = @DON_VI_ID and FLAG = 0

	DECLARE @total_cp int
	SELECT @total_cp = COUNT(*)
	FROM [NV].[KEHOACH]
	WHERE [DON_VI_ID] = @DON_VI_ID
	and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and [STATUS_ID]=@STATUS_ID

	IF(@total = @total_cp)
	BEGIN
	UPDATE [NV].[KEHOACH]
	SET STATUS_ID = (@STATUS_ID+1) 
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=@STATUS_ID and [DON_VI_ID] = @DON_VI_ID
	SELECT '' as TEN_CHI_TIEU_CAP1, '' as TEN_CHI_TIEU_CAP2, '' as TEN_CHI_TIEU_CAP3
	END
	ELSE
	BEGIN

	SELECT ct.[TEN_CHI_TIEU_CAP1], ct.[TEN_CHI_TIEU_CAP2],ct.[TEN_CHI_TIEU_CAP3]
FROM [NV].[DONVI_CHITIEU] dc join [DM].[CHITIEU] ct on dc.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID]
WHERE dc.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
and dc.[DON_VI_ID] = @DON_VI_ID and FLAG=0
and dc.[CHI_TIEU_ID] not in
(SELECT [CHI_TIEU_ID]
FROM [NV].[KEHOACH]
	WHERE [DON_VI_ID] = @DON_VI_ID
	and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and [STATUS_ID]=@STATUS_ID
	)
	END
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_CheckUpdateDieuchinh]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_CheckUpdateDieuchinh]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@NHOMCHITIEU int,
	@NHOM_DON_VI_ID int
AS
BEGIN
	IF EXISTS(SELECT * FROM [NV].[KEHOACH]
	WHERE [NAM_KE_HOACH_ID]= @NAM_KE_HOACH_ID and [STATUS_ID]=@STATUS_ID 
	and CHI_TIEU_ID in (SELECT CHI_TIEU_ID
						FROM [DM].[CHITIEU]
						WHERE [MA_CAP2] = @NHOMCHITIEU and [CAP]=3)
	and [DON_VI_ID] in ( SELECT [DON_VI_ID]
						FROM [DM].[DONVI]
						WHERE [NHOM_DON_VI_ID] = @NHOM_DON_VI_ID
					))
	BEGIN
	SELECT 1
	END
	ELSE
	BEGIN
	SELECT 0
	END
	
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ChinhThuc_ByNamKeHoach]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- @DON_VI_ID > 0 : loc theo nam ke hoach va don vi
CREATE PROCEDURE [dbo].[spKehoach_ChinhThuc_ByNamKeHoach]
    @NHOM_DON_VI_ID int,
    @NAM_KE_HOACH_ID int,
    @STATUS_ID int,
    @DON_VI_ID int,
    @MA_CAP1 varchar(2)
AS
BEGIN
    with t1 as(
        SELECT COALESCE(Convert(float, GIA_TRI),0) as GIA_TRI, 
               kh.CHI_TIEU_ID,                
               ct.TEN_CHI_TIEU, 
               ct.CAP, 
               ct.MA_CAP1, 
               ct.MA_CAP2,
               ct.TEN_CHI_TIEU_CAP1, 
               ct.TEN_CHI_TIEU_CAP2,
               kh.DON_VI_ID, 
               dv.MA_DON_VI
        FROM [NV].[KEHOACH] kh join [DM].[CHITIEU] ct ON
        kh.CHI_TIEU_ID = ct.CHI_TIEU_ID     join DM.DONVI dv
        on kh.DON_VI_ID = dv.DON_VI_ID
        WHERE  [NAM_KE_HOACH_ID]  = @NAM_KE_HOACH_ID and [STATUS_ID]= @STATUS_ID
        and (kh.DON_VI_ID in (select DON_VI_ID from [NV].[DONVI_NHOMDONVI]
                where [NHOM_DON_VI_ID] = @NHOM_DON_VI_ID) OR 
             kh.DON_VI_ID = @DON_VI_ID)
)

select t1.* from t1
where t1.MA_CAP1 = @MA_CAP1
order by t1.CHI_TIEU_ID
END




GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_DonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra chi tieu cua don vi, bao gom toan bo cac chi tieu,
-- cac chi tieu khong thuoc don co gia tri = 0
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_ChiTieu_DonVi]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50),
		LA_CHI_TIEU_AO int
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);
--Toan bo chi tieu cua nam ke hoach
with t1 as (
SELECT distinct 0 AS GIA_TRI,
		k.CHI_TIEU_ID 
		,c.TEN_CHI_TIEU		
		,c.MA
		,c.CAP
		,c.MA_CAP1
		,c.MA_CAP2
	  ,c.DON_VI_TINH
	  ,c.LA_CHI_TIEU_AO
  FROM [NV].[KEHOACH] k  
  Join [DM].[CHITIEU] c ON k.CHI_TIEU_ID = c.CHI_TIEU_ID
  where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = 0 and c.CAP = 3
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1
	  --Chỉ tiêu của đơn vị
	  INSERT INTO @ChiTieuDonVi
	  SELECT distinct COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  
			k.CHI_TIEU_ID		
	  from [NV].[KEHOACH] k
	  where k.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and 
			k.DON_VI_ID = @DON_VI_ID and k.STATUS_ID = @STATUS_ID

	  select * from(
  	  select 
	  COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,
	 -- k.GIA_TRI as GIA_TRI,	  	  
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
		,LA_CHI_TIEU_AO
	  from @DanhSachChiTieu t1 left join @ChiTieuDonVi k
	  ON t1.CHI_TIEU_ID = k.CHI_TIEU_ID	  
	  union
		select distinct 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			  ,c.LA_CHI_TIEU_AO
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_DonVi___test]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra chi tieu cua don vi, bao gom toan bo cac chi tieu,
-- cac chi tieu khong thuoc don co gia tri = 0
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_ChiTieu_DonVi___test]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

with t1 as (
SELECT distinct 0 AS GIA_TRI,
		k.CHI_TIEU_ID 
		,c.TEN_CHI_TIEU		
		,c.MA
		,c.CAP
		,c.MA_CAP1
		,c.MA_CAP2
	  ,c.DON_VI_TINH
  FROM [NV].[KEHOACH] k  
  Join [DM].[CHITIEU] c ON k.CHI_TIEU_ID = c.CHI_TIEU_ID
  where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = 31
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1

	  INSERT INTO @ChiTieuDonVi
	  SELECT COALESCE(Convert(float,k.GIA_TRI),0) as GIA_TRI,	  
			k.CHI_TIEU_ID		
	  from [NV].[KEHOACH] k
	  where k.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and k.DON_VI_ID = @DON_VI_ID and k.STATUS_ID = @STATUS_ID

	  select distinct * from(
  	  select COALESCE(Convert(float,k.GIA_TRI),0) as GIA_TRI,	  
	  --select ISNULL(k.GIA_TRI,0) as GIA_TRI,	  
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
	  from @DanhSachChiTieu t1 left join @ChiTieuDonVi k
	  ON t1.CHI_TIEU_ID = k.CHI_TIEU_ID	  
	 -- union
		--select distinct 0, c.CHI_TIEU_ID
		--	  ,c.TEN_CHI_TIEU
		--	  ,c.MA
		--	  ,c.CAP		
		--	  ,c.MA_CAP1
		--	  ,c.MA_CAP2	  
		--	  ,c.DON_VI_TINH 
		--	from [DM].[CHITIEU] c
		--	where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
		--		  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_DonVi_TheoNhomDonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra chi tieu cua don vi, bao gom toan bo cac chi tieu,
-- cac chi tieu khong thuoc don co gia tri = 0
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_ChiTieu_DonVi_TheoNhomDonVi]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int,
	@NHOM_DON_VI_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50),
		LA_CHI_TIEU_AO int
	);

	Declare @DanhSachChiTieu2 Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50),
		LA_CHI_TIEU_AO int
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

	with t1 as (
		SELECT  distinct 0 AS GIA_TRI,     
			dvct.[CHI_TIEU_ID],
			ct.TEN_CHI_TIEU,
			ct.MA,
			ct.CAP,
			ct.MA_CAP1,
			ct.MA_CAP2,
			ct.DON_VI_TINH,
			ct.LA_CHI_TIEU_AO
		  FROM [NV].[DONVI_CHITIEU] dvct left join [DM].[CHITIEU] ct
		  ON dvct.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID] 
		  --inner Join [NV].[NAMKH_CHITIEU] kh ON dvct.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID] and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
		  where dvct.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID  and ct.TYPE = 1 and
		  (dvct.DON_VI_ID = @DON_VI_ID OR 
			dvct.[DON_VI_ID] in (SELECT DON_VI_ID 
								 From [NV].[DONVI_NHOMDONVI]
							     WHERE NHOM_DON_VI_ID = @NHOM_DON_VI_ID)) 

	)	
	  INSERT INTO @DanhSachChiTieu
	  select * from t1

	  INSERT INTO @ChiTieuDonVi
	  SELECT distinct COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  
			k.CHI_TIEU_ID		
	  from [NV].[KEHOACH] k
	  where k.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and k.DON_VI_ID = @DON_VI_ID and k.STATUS_ID = @STATUS_ID

	  INSERT INTO @DanhSachChiTieu2
		select d.GIA_TRI,     
			ct.[CHI_TIEU_ID],
			ct.TEN_CHI_TIEU,
			ct.MA,
			ct.CAP,
			ct.MA_CAP1,
			ct.MA_CAP2,
			ct.DON_VI_TINH,
			ct.LA_CHI_TIEU_AO
		from @DanhSachChiTieu ct left join @ChiTieuDonVi d
		On ct.[CHI_TIEU_ID] = d.[CHI_TIEU_ID]

		--COALESCE(Convert(float,(
  --    CASE
  --    WHEN IsNumeric(t1.GIA_TRI) = 1 THEN CAST(t1.GIA_TRI AS float) 
  --    ELSE 0
  --    END
  -- )),0) as GIA_TRI

	select * from(
  	  select t1.GIA_TRI ,	  	    
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
		,LA_CHI_TIEU_AO
	  from @DanhSachChiTieu2 t1	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			  ,c.LA_CHI_TIEU_AO
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_Sum]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra ke hoach cua DHQG
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_ChiTieu_Sum]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int
AS
BEGIN

	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50),
		LA_CHI_TIEU_AO int
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

with t1 as (
	SELECT distinct 0 AS GIA_TRI,
		k.CHI_TIEU_ID 
		,c.TEN_CHI_TIEU		
		,c.MA
		,c.CAP
		,c.MA_CAP1
		,c.MA_CAP2
	  ,c.DON_VI_TINH
	  , c.LA_CHI_TIEU_AO
  FROM [NV].[KEHOACH] k  
  Join [DM].[CHITIEU] c ON k.CHI_TIEU_ID = c.CHI_TIEU_ID
  where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = 0 and c.CAP = 3
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1
	
	INSERT INTO @ChiTieuDonVi
	SELECT COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1  THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
	))),0) AS GIA_TRI, 
		k.CHI_TIEU_ID		
	FROM [NV].[KEHOACH] k  join DM.CHITIEU c ON
	k.CHI_TIEU_ID = c.CHI_TIEU_ID
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and k.STATUS_ID = @STATUS_ID and c.CHO_PHEP_TINH_TONG = 1
    GROUP BY k.CHI_TIEU_ID
	union
	(SELECT distinct Convert(float,k.GIA_TRI) as GIA_TRI,
		k.CHI_TIEU_ID 		
	FROM [NV].[KEHOACH] k  
	Join [DM].[CHITIEU] ct ON k.CHI_TIEU_ID = ct.CHI_TIEU_ID
	where ([NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = @STATUS_ID and ct.CAP = 3 and 
	k.CHI_TIEU_ID not in ( SELECT k.CHI_TIEU_ID		
						   FROM [NV].[KEHOACH] k  join DM.CHITIEU c
						   ON k.CHI_TIEU_ID = c.CHI_TIEU_ID
						   WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and 
						   k.STATUS_ID = @STATUS_ID   and c.CAP = 3 and 
						   c.CHO_PHEP_TINH_TONG = 1 
						   GROUP BY k.CHI_TIEU_ID))	or
	ct.CHO_PHEP_TINH_TONG = 0)
	
	  select * from(
  	  select COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  
	  --select ISNULL(k.GIA_TRI,0) as GIA_TRI,	  
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
		,LA_CHI_TIEU_AO
	  from @DanhSachChiTieu t1 left join @ChiTieuDonVi k
	  ON t1.CHI_TIEU_ID = k.CHI_TIEU_ID	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			  ,c.LA_CHI_TIEU_AO
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END




GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_Sum_20160522]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra ke hoach cua DHQG
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_ChiTieu_Sum_20160522]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int
AS
BEGIN

	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50),
		LA_CHI_TIEU_AO int
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

with t1 as (
	SELECT distinct 0 AS GIA_TRI,
		k.CHI_TIEU_ID 
		,c.TEN_CHI_TIEU		
		,c.MA
		,c.CAP
		,c.MA_CAP1
		,c.MA_CAP2
	  ,c.DON_VI_TINH
	  , c.LA_CHI_TIEU_AO
  FROM [NV].[KEHOACH] k  
  Join [DM].[CHITIEU] c ON k.CHI_TIEU_ID = c.CHI_TIEU_ID
  where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = 0 and c.CAP = 3
  )
  --Toan bo chi tieu cua DHQG
	  INSERT INTO @DanhSachChiTieu
	  select * from t1
	
	INSERT INTO @ChiTieuDonVi
	SELECT COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1  THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
	))),0) AS GIA_TRI, 
		k.CHI_TIEU_ID		
	FROM [NV].[KEHOACH] k  join DM.CHITIEU c ON
	k.CHI_TIEU_ID = c.CHI_TIEU_ID
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and k.STATUS_ID = @STATUS_ID and c.CHO_PHEP_TINH_TONG = 1
    GROUP BY k.CHI_TIEU_ID
	union
	(SELECT distinct Convert(float,k.GIA_TRI) as GIA_TRI,
		k.CHI_TIEU_ID 		
	FROM [NV].[KEHOACH] k  
	Join [DM].[CHITIEU] ct ON k.CHI_TIEU_ID = ct.CHI_TIEU_ID
	where ([NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = @STATUS_ID and ct.CAP = 3 and 
	k.CHI_TIEU_ID not in ( SELECT k.CHI_TIEU_ID		
						   FROM [NV].[KEHOACH] k  join DM.CHITIEU c
						   ON k.CHI_TIEU_ID = c.CHI_TIEU_ID
						   WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and 
						   k.STATUS_ID = @STATUS_ID   and c.CAP = 3 and 
						   c.CHO_PHEP_TINH_TONG = 1 
						   GROUP BY k.CHI_TIEU_ID))	or
	ct.CHO_PHEP_TINH_TONG = 0)
	
	  select * from(
  	  select COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  
	  --select ISNULL(k.GIA_TRI,0) as GIA_TRI,	  
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
		,LA_CHI_TIEU_AO
	  from @DanhSachChiTieu t1 left join @ChiTieuDonVi k
	  ON t1.CHI_TIEU_ID = k.CHI_TIEU_ID	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			  ,c.LA_CHI_TIEU_AO
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END




GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_Sum_old]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra ke hoach cua DHQG
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_ChiTieu_Sum_old]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int
AS
BEGIN

	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

with t1 as (
	SELECT distinct 0 AS GIA_TRI,
		k.CHI_TIEU_ID 
		,c.TEN_CHI_TIEU		
		,c.MA
		,c.CAP
		,c.MA_CAP1
		,c.MA_CAP2
	  ,c.DON_VI_TINH
  FROM [NV].[KEHOACH] k  
  Join [DM].[CHITIEU] c ON k.CHI_TIEU_ID = c.CHI_TIEU_ID
  where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = 0 and c.CAP = 3
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1
	
	INSERT INTO @ChiTieuDonVi
	SELECT COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1 THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS GIA_TRI, 
		k.CHI_TIEU_ID		
	FROM [NV].[KEHOACH] k  
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and status_id = @STATUS_ID
    GROUP BY k.CHI_TIEU_ID
	
	  select * from(
  	  select COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  
	  --select ISNULL(k.GIA_TRI,0) as GIA_TRI,	  
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
	  from @DanhSachChiTieu t1 left join @ChiTieuDonVi k
	  ON t1.CHI_TIEU_ID = k.CHI_TIEU_ID	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_TheoDonVi_Sum]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra ke hoach cua DHQG
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_ChiTieu_TheoDonVi_Sum]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@NHOM_DON_VI_ID int
AS
BEGIN

	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @DanhSachChiTieu2 Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

	with t1 as (
	SELECT  distinct 0 AS GIA_TRI,     
		dvct.[CHI_TIEU_ID],
		ct.TEN_CHI_TIEU,
		ct.MA,
		ct.CAP,
		ct.MA_CAP1,
		ct.MA_CAP2,
		ct.DON_VI_TINH
	  FROM [NV].[DONVI_CHITIEU] dvct left join [DM].[CHITIEU] ct
	  ON dvct.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID] inner Join [NV].[NAMKH_CHITIEU] kh 
	  ON dvct.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID] and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	  where dvct.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID  and
	  dvct.[DON_VI_ID] in (SELECT DON_VI_ID From [NV].[DONVI_NHOMDONVI]
						 WHERE NHOM_DON_VI_ID = @NHOM_DON_VI_ID)
	

	
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1
	
	INSERT INTO @ChiTieuDonVi
	SELECT COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1 THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS 
			GIA_TRI, 
			k.CHI_TIEU_ID		
	FROM [NV].[KEHOACH] k  
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and 
		  status_id = @STATUS_ID
    GROUP BY k.CHI_TIEU_ID

	INSERT INTO @DanhSachChiTieu2
	select d.GIA_TRI,     
		ct.[CHI_TIEU_ID],
		ct.TEN_CHI_TIEU,
		ct.MA,
		ct.CAP,
		ct.MA_CAP1,
		ct.MA_CAP2,
		ct.DON_VI_TINH
	from @DanhSachChiTieu ct left join @ChiTieuDonVi d
	On ct.[CHI_TIEU_ID] = d.[CHI_TIEU_ID]
	
	select * from(
  	  select COALESCE(Convert(float,t1.GIA_TRI),0) as GIA_TRI,	  	    
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
	  from @DanhSachChiTieu2 t1	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieu_TheoNhomDonVi_DHQG]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra ke hoach cua DHQG
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_ChiTieu_TheoNhomDonVi_DHQG]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int,
	@NHOM_DON_VI_ID int
AS
BEGIN

	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50),
		LA_CHI_TIEU_AO int
	);

	Declare @DanhSachChiTieu2 Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50),
		LA_CHI_TIEU_AO int
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

	with t1 as (
	SELECT  distinct 0 AS GIA_TRI,     
		dvct.[CHI_TIEU_ID],
		ct.TEN_CHI_TIEU,
		ct.MA,
		ct.CAP,
		ct.MA_CAP1,
		ct.MA_CAP2,
		ct.DON_VI_TINH,
		ct.LA_CHI_TIEU_AO
	  FROM [NV].[DONVI_CHITIEU] dvct left join [DM].[CHITIEU] ct
	  ON dvct.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID] 
	  --inner Join [NV].[NAMKH_CHITIEU] kh ON dvct.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID] and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	  where dvct.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID  and 
	  ct.TYPE = 1 and -- bo sung, vanduong
	  (dvct.DON_VI_ID = @DON_VI_ID OR 
	   dvct.[DON_VI_ID] in (SELECT DON_VI_ID From [NV].[DONVI_NHOMDONVI]
						 WHERE NHOM_DON_VI_ID = @NHOM_DON_VI_ID))
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1
	
	INSERT INTO @ChiTieuDonVi
	SELECT COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1 THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS 
			GIA_TRI, 
			k.CHI_TIEU_ID		
	FROM [NV].[KEHOACH] k  
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and 
		  status_id = @STATUS_ID
    GROUP BY k.CHI_TIEU_ID

	INSERT INTO @DanhSachChiTieu2
	select d.GIA_TRI,     
		ct.[CHI_TIEU_ID],
		ct.TEN_CHI_TIEU,
		ct.MA,
		ct.CAP,
		ct.MA_CAP1,
		ct.MA_CAP2,
		ct.DON_VI_TINH,
		ct.LA_CHI_TIEU_AO
	from @DanhSachChiTieu ct left join @ChiTieuDonVi d
	On ct.[CHI_TIEU_ID] = d.[CHI_TIEU_ID]
	
	select * from(
  	  select t1.GIA_TRI ,	  	    
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
		,LA_CHI_TIEU_AO
	  from @DanhSachChiTieu2 t1	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			  ,c.LA_CHI_TIEU_AO
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_ChiTieuRieng_DonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra chi tieu cua don vi, bao gom toan bo cac chi tieu,
-- cac chi tieu khong thuoc don co gia tri = 0
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_ChiTieuRieng_DonVi]
	@NAM_KE_HOACH_ID int,	
	@STATUS_ID int,
	@DON_VI_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50),
		LA_CHI_TIEU_AO int
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

with t1 as (
SELECT distinct COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,
		k.CHI_TIEU_ID 
		,c.TEN_CHI_TIEU		
		,c.MA
		,c.CAP
		,c.MA_CAP1
		,c.MA_CAP2
		,c.DON_VI_TINH
		,LA_CHI_TIEU_AO
  FROM [NV].[KEHOACH] k join DM.CHITIEU c
	   ON k.CHI_TIEU_ID = c.CHI_TIEU_ID 
	   Join DM.DONVI d 
	   ON k.DON_VI_ID = k.DON_VI_ID 
 WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID =@STATUS_ID and 
		c.TYPE = 2 and k.DON_VI_ID = @DON_VI_ID
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1

	  select * from(
  	  select GIA_TRI,	  	  
		CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
		,LA_CHI_TIEU_AO
	  from @DanhSachChiTieu	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			  ,c.LA_CHI_TIEU_AO
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_Chot_DieuchinhGiuanam]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_Chot_DieuchinhGiuanam]
	@NAM_KE_HOACH_ID int

AS
BEGIN
	BEGIN TRANSACTION
		BEGIN TRY
		DECLARE @STATUS_ID int = 31;
		DECLARE @STATUS_ID_DES int = 61;
		DECLARE @Temp TABLE ( 
		[NAM_KE_HOACH_ID] [int] NOT NULL,
		[DON_VI_ID] [int] NOT NULL,
		[CHI_TIEU_ID] [int] NOT NULL,
		[GIA_TRI] [nvarchar](50) NULL,
		[STATUS_ID] [smallint] NOT NULL,
		[IS_CURRENT] [bit] NOT NULL,
		[NGUOI_DUNG_ID] [int] NOT NULL,
		[STATUS_DATE] [date] NULL
		)

		DECLARE @Comment TABLE (
		[NAM_KE_HOACH_ID] [int] NOT NULL,
		[DON_VI_ID] [int] NOT NULL,
		[CHI_TIEU_ID] [int] NOT NULL,
		[MOC] int,
		[STATUS_ID] int,
		[GIA_TRI] nvarchar(50)
		)

		INSERT INTO @Temp
		SELECT --TOP 1000 [ID],
		[NAM_KE_HOACH_ID]
		,[DON_VI_ID]
		,[CHI_TIEU_ID]
		,[GIA_TRI]
		,[STATUS_ID]
		,[IS_CURRENT]
		,[NGUOI_DUNG_ID]
		,[STATUS_DATE]
		FROM [NV].[KEHOACH]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and
		STATUS_ID = @STATUS_ID
		--Cap nhat STATUS_ID = 61
		UPDATE @Temp
		SET STATUS_ID = @STATUS_ID_DES

		INSERT INTO @Comment
		SELECT [NAM_KE_HOACH_ID] ,
		[DON_VI_ID] ,
		[CHI_TIEU_ID] ,
		[MOC] ,
		[STATUS_ID],
		[GIA_TRI]
		FROM [dbo].[COMMENT]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and
		STATUS_ID = 3 and MOC = 2
		--Update gia_tri tu bang comment
		UPDATE @Temp
		SET [GIA_TRI] = c.GIA_TRI
		FROM @Temp t
		JOIN @Comment c
		ON t.[DON_VI_ID] = c.[DON_VI_ID] and 
		t. [CHI_TIEU_ID] = c.[CHI_TIEU_ID]

		--Insert vao bang hoach voi trang thai 61
		INSERT INTO NV.[KEHOACH]
		SELECT * FROM @Temp
		UPDATE [NV].[NAMKEHOACH]
		SET [STATUS_ID] = 70
		where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID 

		COMMIT TRANSACTION
		END TRY
		BEGIN CATCH
		ROLLBACK TRANSACTION
		END CATCH
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_DanhSachDieuChinh]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_DanhSachDieuChinh]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int
AS
BEGIN
	SELECT 		Count(*) as [COUNT]		
	FROM [NV].[KEHOACH] k  join DM.CHITIEU c ON
	k.CHI_TIEU_ID = c.CHI_TIEU_ID
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and k.STATUS_ID = @STATUS_ID
END

GO
/****** Object:  StoredProcedure [dbo].[spKehoach_DeleteByDONVICHITIEU]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_DeleteByDONVICHITIEU]
	@NAM_KE_HOACH_ID int,
	@CHI_TIEU_ID int,
	@DON_VI_ID int
AS
BEGIN
	DELETE [NV].[KEHOACH] 
	where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID] = @DON_VI_ID and [CHI_TIEU_ID] = @CHI_TIEU_ID and [STATUS_ID] !=0
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_DeleteByNKH]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_DeleteByNKH]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@NHOMCHITIEU int,
	@NHOM_DON_VI_ID int
AS
BEGIN
	if(@NHOMCHITIEU = '-1')
	BEGIN
	DELETE FROM [NV].[KEHOACH]
	WHERE [NAM_KE_HOACH_ID]= @NAM_KE_HOACH_ID and [STATUS_ID]=@STATUS_ID and [DON_VI_ID] in ( SELECT [DON_VI_ID]
						FROM [DM].[DONVI]
						WHERE [NHOM_DON_VI_ID] = @NHOM_DON_VI_ID
					)
	END
	ELSE
	BEGIN
	DELETE FROM [NV].[KEHOACH]
	WHERE [NAM_KE_HOACH_ID]= @NAM_KE_HOACH_ID and [STATUS_ID]=@STATUS_ID 
	and CHI_TIEU_ID in (SELECT CHI_TIEU_ID
						FROM [DM].[CHITIEU]
						WHERE [MA_CAP2] = @NHOMCHITIEU and [CAP]=3)
	and [DON_VI_ID] in ( SELECT [DON_VI_ID]
						FROM [DM].[DONVI]
						WHERE [NHOM_DON_VI_ID] = @NHOM_DON_VI_ID
					)
	END
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_DeletePhanHoi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_DeletePhanHoi]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int,
	@NHOMCHITIEU int

AS
BEGIN
	DELETE FROM [NV].[KEHOACH]
	WHERE [NAM_KE_HOACH_ID]= @NAM_KE_HOACH_ID
	and [STATUS_ID]=@STATUS_ID
	and [DON_VI_ID]=@DON_VI_ID
	and CHI_TIEU_ID in (SELECT CHI_TIEU_ID
						FROM [DM].[CHITIEU]
						WHERE [MA_CAP1] = @NHOMCHITIEU and [CAP]=3)

END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_GetByDV]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_GetByDV]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int
AS
BEGIN
	select  * from [NV].[KEHOACH]
	where [DON_VI_ID] = @DON_VI_ID
	and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and [STATUS_ID]=@STATUS_ID
	order by CHI_TIEU_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_GetByNamKeHoach]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_GetByNamKeHoach]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@FLAG int
AS
BEGIN
	if(@FLAG=0)
	BEGIN
	SELECT * 
	FROM [NV].[KEHOACH]
	WHERE [NAM_KE_HOACH_ID] =@NAM_KE_HOACH_ID and [STATUS_ID]=@STATUS_ID
	END
	if(@FLAG=1)
	BEGIN
	SELECT * 
	FROM [NV].[KEHOACH]
	WHERE [NAM_KE_HOACH_ID] =@NAM_KE_HOACH_ID and [STATUS_ID]=@STATUS_ID
	END

	
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_GetByNamKeHoach_CAP1_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spKehoach_GetByNamKeHoach_CAP1_SUM]
    @NAM_KE_HOACH_ID int,
    @STATUS_ID int
AS
BEGIN
    SELECT  COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1 THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS GIA_TRI, ct.MA_CAP1
    FROM [NV].[KEHOACH] kh join [DM].[CHITIEU] ct
	on kh.CHI_TIEU_ID = ct.CHI_TIEU_ID and ct.MA_CAP1 in (select distinct ct.MA_CAP1 from  [NV].[NAMKH_CHITIEU] nc join [DM].[CHITIEU] ct
	on nc.CHI_TIEU_ID = ct.CHI_TIEU_ID
	where nc.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID)
    WHERE  kh.[NAM_KE_HOACH_ID]  = @NAM_KE_HOACH_ID and kh.[STATUS_ID]=@STATUS_ID 
	GROUP BY ct.MA_CAP1
	ORDER BY ct.MA_CAP1

END




GO
/****** Object:  StoredProcedure [dbo].[spKehoach_GetByNamKeHoach_StatusId_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spKehoach_GetByNamKeHoach_StatusId_SUM]
    @NAM_KE_HOACH_ID int,
    @STATUS_ID int,
    @MA_CAP1 varchar(2)
AS
BEGIN
    with t1 as(
SELECT COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1 THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS GIA_TRI, kh.CHI_TIEU_ID, 
         ct.TEN_CHI_TIEU, ct.CAP, ct.MA_CAP1, ct.MA_CAP2,ct.TEN_CHI_TIEU_CAP1, ct.TEN_CHI_TIEU_CAP2
    FROM [NV].[KEHOACH] kh join [DM].[CHITIEU] ct ON
    kh.CHI_TIEU_ID = ct.CHI_TIEU_ID     
    WHERE  [NAM_KE_HOACH_ID]  = @NAM_KE_HOACH_ID and [STATUS_ID]= @STATUS_ID
    GROUP BY kh.CHI_TIEU_ID , ct.TEN_CHI_TIEU, ct.CAP, ct.MA_CAP1, ct.MA_CAP2,ct.TEN_CHI_TIEU_CAP1, ct.TEN_CHI_TIEU_CAP2
)

select t1.* from t1
where t1.MA_CAP1 = @MA_CAP1
order by t1.CHI_TIEU_ID
END




GO
/****** Object:  StoredProcedure [dbo].[spKehoach_GetByNamKeHoach_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spKehoach_GetByNamKeHoach_SUM]
    @NAM_KE_HOACH_ID int,
    @STATUS_ID int
AS
BEGIN
 SELECT COALESCE(Sum(Convert(float, (
      CASE
      WHEN IsNumeric(GIA_TRI) = 1 THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS GIA_TRI, kh.CHI_TIEU_ID, ct.MA_CAP1
    FROM [NV].[KEHOACH] kh
	join [DM].[CHITIEU] ct
	on kh.CHI_TIEU_ID = ct.CHI_TIEU_ID
    WHERE  kh.[NAM_KE_HOACH_ID]  = @NAM_KE_HOACH_ID and kh.[STATUS_ID]=@STATUS_ID 
    GROUP BY kh.CHI_TIEU_ID ,ct.MA_CAP1
	ORDER BY ct.MA_CAP1

END




GO
/****** Object:  StoredProcedure [dbo].[spKehoach_HuyChot]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_HuyChot]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int

AS
BEGIN
	UPDATE [NV].[KEHOACH]
	SET STATUS_ID = @STATUS_ID
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=(@STATUS_ID+1)  and [DON_VI_ID] = @DON_VI_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_Insert_TABLE]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_Insert_TABLE]
	@Table as [dbo].[KH] readonly
AS
BEGIN
	INSERT INTO [NV].[KEHOACH]([NAM_KE_HOACH_ID],[DON_VI_ID],[CHI_TIEU_ID],[GIA_TRI],[STATUS_ID],[IS_CURRENT],[NGUOI_DUNG_ID],[STATUS_DATE])
	SELECT [NAM_KE_HOACH_ID],[DON_VI_ID],[CHI_TIEU_ID],[GIA_TRI],[STATUS_ID],[IS_CURRENT],[NGUOI_DUNG_ID],[STATUS_DATE] FROM @Table
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_InsertUpdate]
	@ID int,
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int,
	@CHI_TIEU_ID int,
	@GIA_TRI nvarchar(50),
	@STATUS_ID smallint,
	@IS_CURRENT bit,
	@NGUOI_DUNG_ID int,
	@STATUS_DATE date
AS
BEGIN
	IF(@ID = 0)
		BEGIN
			INSERT INTO [NV].[KEHOACH]
			   ([NAM_KE_HOACH_ID]
				,[DON_VI_ID]
				,[CHI_TIEU_ID]
				,[GIA_TRI]
				,[STATUS_ID]
				,[IS_CURRENT]
				,[NGUOI_DUNG_ID]
				,[STATUS_DATE])
			VALUES
			   (
			   @NAM_KE_HOACH_ID,
			   @DON_VI_ID,
			   @CHI_TIEU_ID,
			   @GIA_TRI,
			   @STATUS_ID,
			   @IS_CURRENT,
			   @NGUOI_DUNG_ID,
			   @STATUS_DATE
			   )
		END
	ELSE 
		BEGIN
			UPDATE [NV].[KEHOACH] SET
				[CHI_TIEU_ID]=@CHI_TIEU_ID,
				[GIA_TRI] =@GIA_TRI,
				[STATUS_ID]=@STATUS_ID,
				[IS_CURRENT]=@IS_CURRENT,
				[NGUOI_DUNG_ID]=@NGUOI_DUNG_ID,
				[STATUS_DATE]=@STATUS_DATE
			WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID]=@DON_VI_ID
		END
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_KiemTraChiTieu]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_KiemTraChiTieu] 
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int,
	@STATUS_ID int
AS
BEGIN
	declare @KEHOACH Table(
	[ID] int,
	DON_VI_ID int,
	[CHI_TIEU_ID] int
)
declare @DONVI_CHITIEU Table(
	ID int,
	DON_VI_ID int,
	[CHI_TIEU_ID] int
)

declare @R3 Table(
	ID int,
	DON_VI_ID int,
	[CHI_TIEU_ID] int,
	DonViId int,
	ChiTieuId int
)

	insert into @KEHOACH
	SELECT ID, DON_VI_ID, [CHI_TIEU_ID]
	FROM [NV].[KEHOACH]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=@STATUS_ID and 
	[CHI_TIEU_ID] in (select [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU] 
	where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID]  = @DON_VI_ID and FLAG = 1)
	order by CHI_TIEU_ID

	insert into @DONVI_CHITIEU
	SELECT DON_VI_CHI_TIEU_ID as ID, DON_VI_ID, [CHI_TIEU_ID]
		FROM [NV].[DONVI_CHITIEU]
		where [NAM_KE_HOACH_ID]  = @NAM_KE_HOACH_ID and [FLAG] = 0 and
		[DON_VI_ID] in (Select [DON_VI_ID] from [DM].[DONVI] 
			where [CO_QUYEN_GIAM_SAT] = 0 or [CO_QUYEN_GIAM_SAT] = 2)
		and  [CHI_TIEU_ID] in (select [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU]
		 where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID]  = @DON_VI_ID and FLAG = 1)
			order by CHI_TIEU_ID

	insert into @R3 
	select t.ID, t.DON_VI_ID, t.CHI_TIEU_ID, 
		d.DON_VI_ID as DonViId, d.CHI_TIEU_ID as ChiTieuId
	 from @DONVI_CHITIEU t left join @KEHOACH d
	on t.DON_VI_ID = d.DON_VI_ID and 
	t.CHI_TIEU_ID = d.CHI_TIEU_ID

	select t.ID, 
		t.DON_VI_ID, 
		t.CHI_TIEU_ID,
		d.DON_VI,
		c.TEN_CHI_TIEU
		from @R3 t join [DM].[DONVI] d
		on t.DON_VI_ID = d.DON_VI_ID 
		join [DM].[CHITIEU] c 
		on t.CHI_TIEU_ID = c.CHI_TIEU_ID
	where DonViId is null and ChiTieuId is null
END




GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_Rieng_GetByNamKeHoach]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_Rieng_GetByNamKeHoach]
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int,
	@STATUS_ID int
AS
BEGIN
	SELECT * 
	FROM [NV].[KEHOACH] kh
	join [DM].[CHITIEU] ct on ct.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID]
	WHERE kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and kh.[DON_VI_ID] = @DON_VI_ID and ct.[TYPE] = 2 and [STATUS_ID] = @STATUS_ID

END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_SUM_CANDOI]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spKehoach_SUM_CANDOI]
@NAM_KE_HOACH_ID int,
@STATUS_ID int,
@DON_VI_ID int
AS
BEGIN 
IF(@DON_VI_ID = 30)
begin
select COALESCE(SUM(Convert(float,GIA_TRI)),0) AS GIA_TRI, CHI_TIEU_ID
FROm [NV].[KEHOACH]
where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [STATUS_ID] = @STATUS_ID
GROUP BY CHI_TIEU_ID
ORDER BY CHI_TIEU_ID
end
else
begin
select COALESCE(SUM(Convert(float,GIA_TRI)),0) AS GIA_TRI, CHI_TIEU_ID
FROm [NV].[KEHOACH]
where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [STATUS_ID] = @STATUS_ID
and [CHI_TIEU_ID] in
(select [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU] 
where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID] = @DON_VI_ID and FLAG = 1)
GROUP BY CHI_TIEU_ID
ORDER BY CHI_TIEU_ID
end
END



GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_Checkchot]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: huennb
-- Create date: 20160402
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_Checkchot]
@NAM_KE_HOACH_ID int,
@STATUS_ID int,
@NHOM_CHI_TIEU int,
@MOC_KIEM_SOAT_ID int

AS
BEGIN
DECLARE @total int
SELECT @total= COUNT(*)
FROM [NV].[DONVI_CHITIEU]
WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
and [DON_VI_ID] = @NHOM_CHI_TIEU and FLAG=0

 

DECLARE @total_cp int
SELECT @total_cp= COUNT(*)
FROM [NV].[KEHOACH_THUCHIEN]
WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and (STATUS_ID=@STATUS_ID or STATUS_ID=(@STATUS_ID + 1)) and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID and [DON_VI_ID] = @NHOM_CHI_TIEU


IF(@total = @total_cp)
BEGIN

UPDATE [NV].[KEHOACH_THUCHIEN]
SET [IS_CURRENT] = 0 
WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=(@STATUS_ID+1) 
and [CHI_TIEU_ID] in (SELECT dc.CHI_TIEU_ID from [NV].[DONVI_CHITIEU] dc
						 join [DM].[CHITIEU] ct on dc.CHI_TIEU_ID = ct.CHI_TIEU_ID
						 where dc.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and dc.[DON_VI_ID] = @NHOM_CHI_TIEU and ct.[TYPE] =2)

UPDATE [NV].[KEHOACH_THUCHIEN]
SET STATUS_ID = (@STATUS_ID+1)
WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=@STATUS_ID and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID and [DON_VI_ID] = @NHOM_CHI_TIEU

UPDATE [NV].[KEHOACH_THUCHIEN]
SET [IS_CURRENT] = 1 
WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=(@STATUS_ID+1) and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
and [CHI_TIEU_ID] in (SELECT dc.CHI_TIEU_ID from [NV].[DONVI_CHITIEU] dc
						 join [DM].[CHITIEU] ct on dc.CHI_TIEU_ID = ct.CHI_TIEU_ID
						 where dc.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and dc.[DON_VI_ID] = @NHOM_CHI_TIEU and ct.[TYPE] =2)
SELECT '' as TEN_CHI_TIEU_CAP1, '' as TEN_CHI_TIEU_CAP2, '' as TEN_CHI_TIEU_CAP3

END
ELSE
BEGIN
SELECT ct.[TEN_CHI_TIEU_CAP1], ct.[TEN_CHI_TIEU_CAP2],ct.[TEN_CHI_TIEU_CAP3]
FROM [NV].[DONVI_CHITIEU] dc join [DM].[CHITIEU] ct on dc.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID]
WHERE dc.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
and dc.[DON_VI_ID] = @NHOM_CHI_TIEU and FLAG=0
and dc.[CHI_TIEU_ID] not in
(SELECT [CHI_TIEU_ID]
FROM [NV].[KEHOACH_THUCHIEN]
WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and (STATUS_ID=@STATUS_ID or STATUS_ID=(@STATUS_ID + 1)) and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID and [DON_VI_ID] = @NHOM_CHI_TIEU)
END


END

 




GO
/****** Object:  StoredProcedure [dbo].[spKehoach_Thuchien_CheckChotbyDONVI]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_Thuchien_CheckChotbyDONVI]
	@NAM_KE_HOACH_ID int,
	@MOC_KIEM_SOAT_ID int,
	@DON_VI_ID int
AS
BEGIN
	declare @Count101 int ;
	declare @Count100 int;
   SELECT @count101 = count(*) FROM [NV].[KEHOACH_THUCHIEN]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID 
		and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
		and [STATUS_ID] = 101
		and [DON_VI_ID] =@DON_VI_ID

		SELECT @count100 = count(*) FROM [NV].[KEHOACH_THUCHIEN]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID 
		and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
		and [STATUS_ID] = 100
		and [DON_VI_ID] =@DON_VI_ID

	IF (@Count101 > 0 and @Count100 = 0)
		BEGIN
			SELECT 1
	END
	IF (@Count101 > 0 and @Count100 > 0)
		BEGIN
			SELECT 0
		END
	IF (@Count101 = 0 and @Count100 = 0)
		BEGIN
			SELECT 2
		END
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_CheckFeedBack]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_CheckFeedBack]
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int,
	@MOC_KIEM_SOAT_ID int

AS
BEGIN
	IF exists(select * from [NV].[KEHOACH_THUCHIEN]
	where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and[MOC_KIEM_SOAT_ID]=@MOC_KIEM_SOAT_ID and [STATUS_ID]= 101 and [IS_CURRENT] = 0 and [CHI_TIEU_ID] in (SELECT [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU] where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [DON_VI_ID] = @DON_VI_ID and FLAG = 1))
	begin
	select 1
	end
	else
	begin
	select 0
	end
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_DeleteByNKH]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_DeleteByNKH]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@NHOMCHITIEU int,
	@NHOM_DON_VI_ID int,
	@MOC_KIEM_SOAT_ID int
AS
BEGIN
	if(@NHOMCHITIEU = '-1')
	BEGIN
	DELETE FROM [NV].[KEHOACH_THUCHIEN]
	WHERE [NAM_KE_HOACH_ID]= @NAM_KE_HOACH_ID and [STATUS_ID]=@STATUS_ID and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
	END
	ELSE
	BEGIN
	DELETE FROM [NV].[KEHOACH_THUCHIEN]
	WHERE [NAM_KE_HOACH_ID]= @NAM_KE_HOACH_ID and [STATUS_ID]=@STATUS_ID and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
	and CHI_TIEU_ID in (SELECT CHI_TIEU_ID
						FROM [DM].[CHITIEU]
						WHERE [MA_CAP1] = @NHOMCHITIEU and [CAP]=3)
	and [DON_VI_ID] = @NHOM_DON_VI_ID
	END
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_Getall_by_nkh]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_Getall_by_nkh]
	@NAM_KE_HOACH_ID int,
	@FLAG int
AS
BEGIN
	IF(@FLAG = 0)
	BEGIN
	SELECT * FROM [NV].[KEHOACH_THUCHIEN]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [IS_CURRENT] =1
	END
	ELSE
	BEGIN
	SELECT * FROM [NV].[KEHOACH_THUCHIEN]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [IS_CURRENT] =0 AND [STATUS_ID]=101
	END
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_Getall_by_nkh_mockiemsoat]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_Getall_by_nkh_mockiemsoat]
	@NAM_KE_HOACH_ID int,
	@MOC_KIEM_SOAT_ID int,
	@NHOM_DON_VI_ID int,
	@FLAG int
AS
BEGIN
	IF(@FLAG = 0)
	BEGIN
	SELECT * FROM [NV].[KEHOACH_THUCHIEN]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID and
	DON_VI_ID in (SELECT  d.DON_VI_ID
		FROM [DM].[DONVI] d
		JOIN [NV].[DONVI_NHOMDONVI] m
		on d.DON_VI_ID = m.DON_VI_ID
		WHERE m.[NHOM_DON_VI_ID] = @NHOM_DON_VI_ID)
	END
	ELSE
	BEGIN
		SELECT * FROM [NV].[KEHOACH_THUCHIEN]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID and
		DON_VI_ID  = @NHOM_DON_VI_ID
	END
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_GetByDV]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_GetByDV]
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int
AS
BEGIN
	select * from [NV].[KEHOACH_THUCHIEN]
	where [DON_VI_ID] = @DON_VI_ID
	and [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and IS_CURRENT =1
	order by CHI_TIEU_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoach_Thuchien_GetByNamKeHoach]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoach_Thuchien_GetByNamKeHoach]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@MOC_KIEM_SOAT_ID int
AS
BEGIN
	SELECT * 
	FROM [NV].[KEHOACH_THUCHIEN]
	WHERE [NAM_KE_HOACH_ID] =@NAM_KE_HOACH_ID and [STATUS_ID]=@STATUS_ID and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_GetByNamKeHoach_BaoCao]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_GetByNamKeHoach_BaoCao]
    @NAM_KE_HOACH_ID int,
    @MA_CAP1 varchar(2)    ,
    @NHOM_DON_VI_ID int,
    @DON_VI_ID int
AS
BEGIN
with t1 as(
    SELECT  COALESCE( Convert(float,GIA_TRI),0) as GIA_TRI, 
            kh.CHI_TIEU_ID,             
            ct.TEN_CHI_TIEU,            
            ct.CAP, 
            ct.MA_CAP1, 
            ct.MA_CAP2,
            ct.TEN_CHI_TIEU_CAP1, 
            ct.TEN_CHI_TIEU_CAP2,
            dv.DON_VI_ID,
            dv.MA_DON_VI
            FROM [NV].[KEHOACH_THUCHIEN] kh join [DM].[CHITIEU] ct
            on kh.CHI_TIEU_ID = ct.CHI_TIEU_ID    join 
            DM.DONVI dv oN kh.DON_VI_ID = dv.DON_VI_ID
            WHERE  [NAM_KE_HOACH_ID]  = @NAM_KE_HOACH_ID  and [IS_CURRENT] = 1 
            and (kh.DON_VI_ID in (select DON_VI_ID From [NV].[DONVI_NHOMDONVI]
                                where [NHOM_DON_VI_ID] = @NHOM_DON_VI_ID) OR
                 kh.DON_VI_ID = @DON_VI_ID)
            )
    select t1.* from t1
    where t1.MA_CAP1 = @MA_CAP1
    order by t1.CHI_TIEU_ID
END




GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_GetByNamKeHoach_CAP1_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_GetByNamKeHoach_CAP1_SUM]
    @NAM_KE_HOACH_ID int
AS
BEGIN
    SELECT  COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1 THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS GIA_TRI, ct.MA_CAP1
    FROM [NV].[KEHOACH_THUCHIEN] kh join [DM].[CHITIEU] ct
	on kh.CHI_TIEU_ID = ct.CHI_TIEU_ID and ct.MA_CAP1 in (select distinct ct.MA_CAP1 from  [NV].[NAMKH_CHITIEU] nc join [DM].[CHITIEU] ct
	on nc.CHI_TIEU_ID = ct.CHI_TIEU_ID
	where nc.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID)
    WHERE  kh.[NAM_KE_HOACH_ID]  = @NAM_KE_HOACH_ID and  kh.[IS_CURRENT] = 1
	GROUP BY ct.MA_CAP1
	ORDER BY ct.MA_CAP1

END




GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_GetByNamKeHoach_NhomDonViID_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_GetByNamKeHoach_NhomDonViID_SUM]
    @NAM_KE_HOACH_ID int,
    @MA_CAP1 varchar(2)    
AS
BEGIN
with t1 as(
    SELECT COALESCE(SUM( Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1 THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS 
            GIA_TRI, 
            kh.CHI_TIEU_ID, 
            ct.TEN_CHI_TIEU,
            ct.CAP, 
            ct.MA_CAP1, 
            ct.MA_CAP2,
            ct.TEN_CHI_TIEU_CAP1, 
            ct.TEN_CHI_TIEU_CAP2
            FROM [NV].[KEHOACH_THUCHIEN] kh join [DM].[CHITIEU] ct
            on kh.CHI_TIEU_ID = ct.CHI_TIEU_ID            
            WHERE  [NAM_KE_HOACH_ID]  = @NAM_KE_HOACH_ID  and [IS_CURRENT] = 1
            GROUP BY kh.CHI_TIEU_ID , 
                     ct.TEN_CHI_TIEU, 
                     ct.CAP,
                     ct.MA_CAP1, 
                     ct.MA_CAP2,
                     ct.TEN_CHI_TIEU_CAP1, 
                     ct.TEN_CHI_TIEU_CAP2
            )
    select t1.* from t1
    where t1.MA_CAP1 = @MA_CAP1
    order by t1.CHI_TIEU_ID
END




GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_GetByNamKeHoach_SUM]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_GetByNamKeHoach_SUM]
    @NAM_KE_HOACH_ID int,
	@FLAG int
AS
BEGIN
	if(@FLAG=0)
	begin
   SELECT COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1 THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS GIA_TRI, kt.CHI_TIEU_ID,ct.MA_CAP1
  FROM [NV].[KEHOACH_THUCHIEN] kt
  join [DM].[CHITIEU] ct
  on kt.CHI_TIEU_ID = ct.CHI_TIEU_ID
  WHERE kt.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and kt.[IS_CURRENT] = 1
  GROUP BY kt.CHI_TIEU_ID ,ct.MA_CAP1
  ORDER BY ct.MA_CAP1
  end
  else
  begin
	  SELECT COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(GIA_TRI) = 1 THEN CAST(GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS GIA_TRI, kt.CHI_TIEU_ID,ct.MA_CAP1
	  FROM [NV].[KEHOACH_THUCHIEN] kt
	  join [DM].[CHITIEU] ct
	  on kt.CHI_TIEU_ID = ct.CHI_TIEU_ID
	  WHERE kt.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and kt.[IS_CURRENT] = 0 and kt.[STATUS_ID] = 101 
	  GROUP BY kt.CHI_TIEU_ID ,ct.MA_CAP1
	  ORDER BY ct.MA_CAP1
  end


END




GO
/****** Object:  StoredProcedure [dbo].[spKehoach_Thuchien_HuychotbyDONVI]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_Thuchien_HuychotbyDONVI]
	@NAM_KE_HOACH_ID int,
	@MOC_KIEM_SOAT_ID int,
	@DON_VI_ID int
AS
BEGIN
	IF not exists(
		SELECT * FROM [NV].[KEHOACH_THUCHIEN]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID 
		and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
		and [STATUS_ID] = 101
		and IS_CURRENT = 0 
		and [DON_VI_ID] =@DON_VI_ID
	)
	BEGIN
	SELECT * FROM [NV].[KEHOACH_THUCHIEN]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID 
		and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
		and [STATUS_ID] = 101
		and IS_CURRENT = 0 
		and [DON_VI_ID] =@DON_VI_ID
	END
	ELSE
	BEGIN
	with t1 as(
				SELECT ct. * FROM [NV].[KEHOACH_THUCHIEN] kt
						join [DM].[CHITIEU] ct on kt.CHI_TIEU_ID = ct.CHI_TIEU_ID
						WHERE kt.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID 
						and kt.[MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
						and kt.[STATUS_ID] = 101
						and kt.IS_CURRENT = 0 
						and kt.[DON_VI_ID] =@DON_VI_ID
			)
		
				SELECT * FROM [DM].[CHITIEU] ct 
				WHERE (MA_CAP1 in (select distinct MA_CAP1 from t1) and CAP = 1) OR  
					  (
						MA_CAP1 in (select distinct MA_CAP1 from t1 ) and 
						MA_CAP2 in (select distinct MA_CAP2 from t1 ) and CAP = 2
					   ) 
				UNION
				SELECT * FROM t1
	UPDATE [NV].[KEHOACH_THUCHIEN]
	SET [STATUS_ID] = 100
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID 
		and [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
		and [STATUS_ID] = 101
		and IS_CURRENT = 0 
		and [DON_VI_ID] =@DON_VI_ID
	END
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_ThucHien_Rieng_Getall_by_nkh]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_ThucHien_Rieng_Getall_by_nkh]
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int
AS
BEGIN
	BEGIN
	SELECT * FROM [NV].[KEHOACH_THUCHIEN]
	WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [IS_CURRENT] =1 and [CHI_TIEU_ID] in
	(SELECT dc.CHI_TIEU_ID from [NV].[DONVI_CHITIEU] dc
						 join [DM].[CHITIEU] ct on dc.CHI_TIEU_ID = ct.CHI_TIEU_ID
						 where dc.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and dc.[DON_VI_ID] = @DON_VI_ID and ct.[TYPE] =2)
	END
END





GO
/****** Object:  StoredProcedure [dbo].[spKehoach_UpdateCURRENT]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKehoach_UpdateCURRENT]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID smallint
AS
BEGIN
	UPDATE  [NV].[KEHOACH]
	SET [IS_CURRENT]=1
	WHERE [NAM_KE_HOACH_ID] =@NAM_KE_HOACH_ID and [STATUS_ID]=@STATUS_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieu_DonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra chi tieu cua don vi, bao gom toan bo cac chi tieu,
-- cac chi tieu khong thuoc don co gia tri = 0
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_ChiTieu_DonVi]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

with t1 as (
SELECT distinct 0 AS GIA_TRI,
		k.CHI_TIEU_ID 
		,c.TEN_CHI_TIEU		
		,c.MA
		,c.CAP
		,c.MA_CAP1
		,c.MA_CAP2
	  ,c.DON_VI_TINH
  FROM [NV].[KEHOACH] k  
  Join [DM].[CHITIEU] c ON k.CHI_TIEU_ID = c.CHI_TIEU_ID
  where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = 0 and c.CAP = 3
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1

	  INSERT INTO @ChiTieuDonVi
	  SELECT COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  
			k.CHI_TIEU_ID		
	  from [NV].[KEHOACH_THUCHIEN] k
	  where k.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and k.DON_VI_ID = @DON_VI_ID  and IS_CURRENT = 1

	  select * from(
  	  select COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  
	  --select ISNULL(k.GIA_TRI,0) as GIA_TRI,	  
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
	  from @DanhSachChiTieu t1 left join @ChiTieuDonVi k
	  ON t1.CHI_TIEU_ID = k.CHI_TIEU_ID	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieu_DonVi_TheoNhomDonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra chi tieu cua don vi, bao gom toan bo cac chi tieu,
-- cac chi tieu khong thuoc don co gia tri = 0
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_ChiTieu_DonVi_TheoNhomDonVi]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int,
	@NHOM_DON_VI_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @DanhSachChiTieu2 Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

with t1 as (
	SELECT  distinct 0 AS GIA_TRI,     
		dvct.[CHI_TIEU_ID],
		ct.TEN_CHI_TIEU,
		ct.MA,
		ct.CAP,
		ct.MA_CAP1,
		ct.MA_CAP2,
		ct.DON_VI_TINH
	  FROM [NV].[DONVI_CHITIEU] dvct left join [DM].[CHITIEU] ct
	  ON dvct.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID] inner Join [NV].[NAMKH_CHITIEU] kh 
	  ON dvct.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID] and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	  where dvct.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID  and 
	  ( dvct.DON_VI_ID = @DON_VI_ID OR
	  dvct.[DON_VI_ID] in (SELECT DON_VI_ID From [NV].[DONVI_NHOMDONVI]
						 WHERE NHOM_DON_VI_ID = @NHOM_DON_VI_ID))
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1

	  INSERT INTO @ChiTieuDonVi
	  SELECT COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  
			k.CHI_TIEU_ID		
	  from [NV].[KEHOACH_THUCHIEN] k
	  where k.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and k.DON_VI_ID = @DON_VI_ID  and IS_CURRENT = 1

	  INSERT INTO @DanhSachChiTieu2
		select d.GIA_TRI,     
			ct.[CHI_TIEU_ID],
			ct.TEN_CHI_TIEU,
			ct.MA,
			ct.CAP,
			ct.MA_CAP1,
			ct.MA_CAP2,
			ct.DON_VI_TINH
		from @DanhSachChiTieu ct left join @ChiTieuDonVi d
		On ct.[CHI_TIEU_ID] = d.[CHI_TIEU_ID]

	  select * from(
  	  select COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(t1.GIA_TRI) = 1 THEN CAST(t1.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  	    
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
	  from @DanhSachChiTieu2 t1	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieu_Sum]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160427
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_ChiTieu_Sum]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

with t1 as 
(
	SELECT k.CHI_TIEU_ID
		,c.TEN_CHI_TIEU		
		,c.MA
		,c.CAP
		,c.MA_CAP1
		,c.MA_CAP2
		,c.DON_VI_TINH
	 FROM [NV].[KEHOACH] k
	 Join [DM].[DONVI] d ON k.DON_VI_ID = d.DON_VI_ID
	 Join [DM].[CHITIEU] c ON k.CHI_TIEU_ID = c.CHI_TIEU_ID
	 WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = 0 and c.CAP = 3
		GROUP BY k.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU		  
			  ,c.MA
			  ,c.CAP		  
			  ,c.MA_CAP1
			  ,c.MA_CAP2
			  ,c.DON_VI_TINH  
)
		INSERT INTO @DanhSachChiTieu
		SELECT  0 as GIA_TRI,
			CHI_TIEU_ID
			,TEN_CHI_TIEU		
			,MA
			,CAP
			,MA_CAP1
			,MA_CAP2
			,DON_VI_TINH
		FROM t1
			
		INSERT INTO @ChiTieuDonVi
		SELECT COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS GIA_TRI, 
		      k.CHI_TIEU_ID			  
		FROM [NV].[KEHOACH_THUCHIEN] k
		WHERE k.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID and IS_CURRENT = 1
		GROUP BY k.CHI_TIEU_ID			   
		
	SELECT *  FROM (
		select COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  	  
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
	  from @DanhSachChiTieu t1 left join @ChiTieuDonVi k
	  ON t1.CHI_TIEU_ID = k.CHI_TIEU_ID	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieu_Sum_TheoNhomDonVi_DHQG]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160427
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_ChiTieu_Sum_TheoNhomDonVi_DHQG]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@DON_VI_ID int,
	@NHOM_DON_VI_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @DanhSachChiTieu2 Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

with t1 as (
	SELECT  distinct 0 AS GIA_TRI,     
		dvct.[CHI_TIEU_ID],
		ct.TEN_CHI_TIEU,
		ct.MA,
		ct.CAP,
		ct.MA_CAP1,
		ct.MA_CAP2,
		ct.DON_VI_TINH
	  FROM [NV].[DONVI_CHITIEU] dvct left join [DM].[CHITIEU] ct
	  ON dvct.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID] inner Join [NV].[NAMKH_CHITIEU] kh 
	  ON dvct.[CHI_TIEU_ID] = kh.[CHI_TIEU_ID] and kh.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	  where dvct.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID  and
	  (dvct.DON_VI_ID = @DON_VI_ID OR
	  dvct.[DON_VI_ID] in (SELECT DON_VI_ID From [NV].[DONVI_NHOMDONVI]
						 WHERE NHOM_DON_VI_ID = @NHOM_DON_VI_ID))
	
  )	
	  INSERT INTO @DanhSachChiTieu
	  select * from t1
			
		INSERT INTO @ChiTieuDonVi
		SELECT COALESCE(SUM(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   ))),0) AS 
			  GIA_TRI, 
		      k.CHI_TIEU_ID			  
		FROM [NV].[KEHOACH_THUCHIEN] k
		WHERE k.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID and IS_CURRENT = 1
		GROUP BY k.CHI_TIEU_ID			   
		
		
	  INSERT INTO @DanhSachChiTieu2
		select d.GIA_TRI,     
			ct.[CHI_TIEU_ID],
			ct.TEN_CHI_TIEU,
			ct.MA,
			ct.CAP,
			ct.MA_CAP1,
			ct.MA_CAP2,
			ct.DON_VI_TINH
		from @DanhSachChiTieu ct left join @ChiTieuDonVi d
		On ct.[CHI_TIEU_ID] = d.[CHI_TIEU_ID]

		select * from(
  	  select COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(t1.GIA_TRI) = 1 THEN CAST(t1.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,	  	    
		t1.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
	  from @DanhSachChiTieu2 t1	  
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieuRieng_DonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra chi tieu cua don vi, bao gom toan bo cac chi tieu,
-- cac chi tieu khong thuoc don co gia tri = 0
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_ChiTieuRieng_DonVi]
	@NAM_KE_HOACH_ID int,	
	@DON_VI_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI nvarchar(50),
		CHI_TIEU_ID int		
	);

with t1 as (
SELECT distinct COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,
		k.CHI_TIEU_ID 
		,c.TEN_CHI_TIEU		
		,c.MA
		,c.CAP
		,c.MA_CAP1
		,c.MA_CAP2
	  ,c.DON_VI_TINH
  FROM [NV].[KEHOACH] k join DM.CHITIEU c
	   ON k.CHI_TIEU_ID = c.CHI_TIEU_ID Join DM.DONVI d 
	   ON k.DON_VI_ID = k.DON_VI_ID 
 WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = 21  and 
		c.TYPE = 2 and k.DON_VI_ID = @DON_VI_ID
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1

	  INSERT INTO @ChiTieuDonVi
	  select COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,
		k.CHI_TIEU_ID
	  FROM [NV].[KEHOACH_THUCHIEN] k join DM.CHITIEU c
	   ON k.CHI_TIEU_ID = c.CHI_TIEU_ID Join DM.DONVI d 
	   ON k.DON_VI_ID = k.DON_VI_ID 
	  WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [IS_CURRENT] =1  and 
		c.TYPE = 2 and k.DON_VI_ID = @DON_VI_ID

	  select * from(
  	  select c.GIA_TRI,	  	  
		d.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
	  from @DanhSachChiTieu d left join @ChiTieuDonVi c
	  ON d.CHI_TIEU_ID = c.CHI_TIEU_ID
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_Delete]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_Delete]
    @KH_THUC_HIEN_ID int
AS
BEGIN
	DELETE FROM [NV].[KEHOACH_THUCHIEN]
	WHERE [KH_THUC_HIEN_ID] = @KH_THUC_HIEN_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_GetById]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_GetById]
    @KH_THUC_HIEN_ID int
AS
BEGIN
	SELECT [KH_THUC_HIEN_ID]
      ,[NAM_KE_HOACH_ID]
      ,[DON_VI_ID]
      ,[CHI_TIEU_ID]
      ,[GIA_TRI]
      ,[MOC_KIEM_SOAT_ID]
      ,[NGUOI_DUNG_ID]
      ,[LASTUPDATED]
  FROM [KEHOACH_THUCHIEN]
  WHERE [KH_THUC_HIEN_ID] = @KH_THUC_HIEN_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong	
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_GetListPaging]
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int,
	@CHI_TIEU_ID int,
	@GIA_TRI nvarchar (50),
	@MOC_KIEM_SOAT_ID int,
	@NGUOI_DUNG_ID int,
	@PAGE_INDEX INT,
	@PAGE_SIZE INT
AS
BEGIN
   WITH t1 as (
		SELECT  
			ROW_NUMBER() OVER(ORDER BY t1.KH_THUC_HIEN_ID DESC) AS NUMBER,
			[KH_THUC_HIEN_ID]
			,[NAM_KE_HOACH_ID]
			,[DON_VI_ID]
			,[CHI_TIEU_ID]
			,[GIA_TRI]
			,[MOC_KIEM_SOAT_ID]
			,[NGUOI_DUNG_ID]
			,[LASTUPDATED]
  FROM [KEHOACH_THUCHIEN] khth 
  join NV.NAMKEHOACH nkh on khth.[NAM_KE_HOACH_ID] = nkh.NAM_KE_HOACH_ID
  join DM.DONVI dv on khth.DON_VI_ID = dv.DON_VI_ID 
  join DM.CHITIEU ct on khth.CHI_TIEU_ID = ct.CHI_TIEU_ID
  join NV.MOCKIEMSOAT mks on khth.MOC_KIEM_SOAT_ID = mks.MOC_KIEM_SOAT_ID
  join DM.NGUOIDUNG nd on khth.NGUOI_DUNG_ID = nd.NGUOI_DUNG_ID
  where 
		(@NAM_KE_HOACH_ID = 0 OR [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID) AND
		(@DON_VI_ID = 0 OR [DON_VI_ID] = @DON_VI_ID) AND
		(@CHI_TIEU_ID = 0 OR [CHI_TIEU_ID] = @CHI_TIEU_ID) AND
		(@GIA_TRI = NULL OR LOWER([GIA_TRI]) LIKE LOWER('%'+@GIA_TRI+'%') COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI) AND
		(@MOC_KIEM_SOAT_ID = 0 OR [MOC_KIEM_SOAT_ID]= @MOC_KIEM_SOAT_ID) AND
		(@NGUOI_DUNG_ID = 0 OR [NGUOI_DUNG_ID] = @NGUOI_DUNG_ID)
   )

   SELECT t1.*, (SELECT COUNT(*) FROM t1) AS RECORD_COUNT
   FROM t1
   WHERE NUMBER BETWEEN (@PAGE_INDEX * @PAGE_SIZE + 1) AND ((@PAGE_INDEX + 1) * @PAGE_SIZE)
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_Insert]
	@Table as [dbo].[KEHOACHTHUCHIEN] readonly
AS
BEGIN
	INSERT INTO [NV].[KEHOACH_THUCHIEN](
		[NAM_KE_HOACH_ID]
		,[DON_VI_ID]
		,[CHI_TIEU_ID]
		,[GIA_TRI]
		,[MOC_KIEM_SOAT_ID]
		,[NGUOI_DUNG_ID]
		,[LASTUPDATED]
		,[STATUS_ID]
		,[IS_CURRENT]
	)
	SELECT 
		[NAM_KE_HOACH_ID]
		,[DON_VI_ID]
		,[CHI_TIEU_ID]
		,[GIA_TRI]
		,[MOC_KIEM_SOAT_ID]
		,[NGUOI_DUNG_ID]
		,[LASTUPDATED]
		,[STATUS_ID]
		,[IS_CURRENT]
	FROM @TABLE
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_InsertUpdate]
    @KH_THUC_HIEN_ID int,
	@NAM_KE_HOACH_ID int,
	@DON_VI_ID int,
	@CHI_TIEU_ID int,
	@GIA_TRI nvarchar (50),
	@MOC_KIEM_SOAT_ID int,
	@NGUOI_DUNG_ID int
AS
BEGIN
	if(@KH_THUC_HIEN_ID = 0)
		BEGIN
			INSERT INTO [NV].[KEHOACH_THUCHIEN]
				   ([KH_THUC_HIEN_ID]
				   ,[NAM_KE_HOACH_ID]
				   ,[DON_VI_ID]
				   ,[CHI_TIEU_ID]
				   ,[GIA_TRI]
				   ,[MOC_KIEM_SOAT_ID]
				   ,[NGUOI_DUNG_ID]
				   ,[LASTUPDATED])
			 VALUES
				   (@KH_THUC_HIEN_ID
				   ,@NAM_KE_HOACH_ID
				   ,@DON_VI_ID
				   ,@CHI_TIEU_ID
				   ,@GIA_TRI
				   ,@MOC_KIEM_SOAT_ID
				   ,@NGUOI_DUNG_ID
				   ,getdate())
		END
	else 
		BEGIN	
			UPDATE [NV].[KEHOACH_THUCHIEN] SET
				   
				   [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
				   ,[DON_VI_ID] = @DON_VI_ID
				   ,[CHI_TIEU_ID] = @CHI_TIEU_ID
				   ,[GIA_TRI] = @GIA_TRI
				   ,[MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
				   ,[NGUOI_DUNG_ID]= @NGUOI_DUNG_ID
				   ,[LASTUPDATED] = getdate()
			 
		END
END





GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_Update]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spKeHoachThucHien_Update]
	@Table as [dbo].[KEHOACH_THUCHIEN] readonly
AS
BEGIN
	UPDATE [NV].[KEHOACH_THUCHIEN]
	SET [NAM_KE_HOACH_ID] = t.NAM_KE_HOACH_ID,
	[DON_VI_ID] = t.DON_VI_ID,
	[CHI_TIEU_ID] = t.CHI_TIEU_ID,
	[GIA_TRI] = t.GIA_TRI,
	[MOC_KIEM_SOAT_ID] = t.MOC_KIEM_SOAT_ID,
	[NGUOI_DUNG_ID] = t.NGUOI_DUNG_ID,
	[LASTUPDATED] = t.LASTUPDATED
	FROM [NV].[KEHOACH_THUCHIEN] k JOIN @Table t 
	ON k.[KH_THUC_HIEN_ID] = t.KH_THUCHIEN_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_CheckBanChot]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spMocKiemSoat_CheckBanChot]
	@NAM_KE_HOACH_ID int,
	@MOC_KIEM_SOAT_ID int

AS
BEGIN
	SELECT distinct dv.DON_VI
  FROM [NV].[KEHOACH_THUCHIEN] kt
  left join [NV].[KEHOACH] kt1 on kt1.CHI_TIEU_ID = kt.CHI_TIEU_ID and kt1.STATUS_ID = 0 and kt1.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID 
  join [DM].[DONVI] dv on kt1.DON_VI_ID = dv.DON_VI_ID
  where kt.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and kt.[MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID and kt.STATUS_ID=101 and kt.IS_CURRENT = 0
END





GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_CheckThucHien]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spMocKiemSoat_CheckThucHien]
	@NAM_KE_HOACH_ID int,
	@MOC_KIEM_SOAT_ID int

AS
BEGIN
	select distinct dv.[DON_VI] from [NV].[DONVI_CHITIEU] dc join [DM].[DONVI] dv
		on dv.[DON_VI_ID] = dc.[DON_VI_ID]
		where dc.[NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID and dc.FLAG = 0 and dc.DON_VI_ID in (SELECT DON_VI_ID from  [DM].[DONVI] where [CO_QUYEN_GIAM_SAT] = 0 or [CO_QUYEN_GIAM_SAT] = 2)
		and dc.DON_VI_ID not in(select DON_VI_ID from [NV].[KEHOACH_THUCHIEN] 
							where [NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID and [MOC_KIEM_SOAT_ID] =@MOC_KIEM_SOAT_ID and [STATUS_ID]=101 )
END





GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_DELETE]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spMocKiemSoat_DELETE]
	@MOC_KIEM_SOAT_ID int

AS
BEGIN
	DELETE  FROM [NV].[MOCKIEMSOAT]
	WHERE [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_GetBefo]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spMocKiemSoat_GetBefo]
	@NAM_KE_HOACH_ID int,
	@MOC_KIEM_SOAT_ID int
AS
BEGIN
	select top 1 * from [NV].[MOCKIEMSOAT]
	where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [THOI_HAN] < (select [THOI_HAN] from [NV].[MOCKIEMSOAT] where [MOC_KIEM_SOAT_ID] = @MOC_KIEM_SOAT_ID)
	order by [THOI_HAN] desc
END





GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_GetByID]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spMocKiemSoat_GetByID]
	@NAM_KE_HOACH_ID int

AS
BEGIN
	SELECT * FROM [NV].[MOCKIEMSOAT]
	WHERE [NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_GetByID_NEW]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spMocKiemSoat_GetByID_NEW]
	@MOC_KIEM_SOAT_ID int

AS
BEGIN
	SELECT * FROM [NV].[MOCKIEMSOAT]
	WHERE [MOC_KIEM_SOAT_ID]=@MOC_KIEM_SOAT_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spMocKiemSoat_InsertUpdate]
	@MOC_KIEM_SOAT_ID int,
	@MOC_KIEM_SOAT nvarchar(250),
	@THOI_HAN date,
	@NAM_KE_HOACH_ID int,
	@IS_ACTIVE int

AS
BEGIN
	IF(@MOC_KIEM_SOAT_ID = 0)
	BEGIN
		INSERT INTO [NV].[MOCKIEMSOAT](
		[MOC_KIEM_SOAT],
		[THOI_HAN],
		[NAM_KE_HOACH_ID],
		[IS_ACTIVE]
		)
		VALUES(
		@MOC_KIEM_SOAT,
		@THOI_HAN,
		@NAM_KE_HOACH_ID,
		@IS_ACTIVE
		)
		Select IDENT_CURRENT('[NV].[MOCKIEMSOAT]')
	END
	ELSE
	BEGIN
		IF(@IS_ACTIVE =1)
		BEGIN
		UPDATE [NV].[MOCKIEMSOAT] SET
		[IS_ACTIVE]=0
		WHERE 
		[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID

		UPDATE [NV].[MOCKIEMSOAT] SET
		[MOC_KIEM_SOAT]=@MOC_KIEM_SOAT,
		[THOI_HAN]=@THOI_HAN,
		[IS_ACTIVE]=1
		WHERE [MOC_KIEM_SOAT_ID]=@MOC_KIEM_SOAT_ID
		SELECT 22
		END
		

	END

END





GO
/****** Object:  StoredProcedure [dbo].[spMocKiemSoat_IS_ACTIVE]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spMocKiemSoat_IS_ACTIVE]
	@NAM_KE_HOACH_ID int

AS
BEGIN
	SELECT * FROM [NV].[MOCKIEMSOAT]
	WHERE [NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID and IS_ACTIVE = 1
END





GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoach_Check]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNamKeHoach_Check]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int

AS
BEGIN
	IF(@STATUS_ID = 0 )
	BEGIN
		IF NOT EXISTS(SELECT * FROM [NV].[DONVI_CHITIEU] WHERE [NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID and DON_VI_ID in
		(SELECT DON_VI_ID 
		FROM [DM].[DONVI]
		WHERE [CO_QUYEN_GIAM_SAT] = 0)
		)
		BEGIN
		Select 1
		END
		IF NOT EXISTS(SELECT * FROM [NV].[MOCKIEMSOAT] WHERE [NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID)
		BEGIN
		Select 2
		END
		IF NOT EXISTS(SELECT * FROM [NV].[NAMKH_CHITIEU] WHERE [NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID)
		BEGIN
		Select 3
		END
	END
	ELSE
	BEGIN
		DECLARE @total int
		DECLARE @total_cp int
		select @total= COUNT(*) from [NV].[DONVI_CHITIEU] dc
		join [DM].[CHITIEU] ct on dc.[CHI_TIEU_ID] = ct.[CHI_TIEU_ID] 
		where dc.[NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID and dc.FLAG = 0 and ct.[TYPE]=1
		SELECT @total_cp= COUNT(*)
		FROM [NV].[KEHOACH]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [STATUS_ID] = (@STATUS_ID + 1)
		IF(@total != @total_cp)
		BEGIN
		SELECT 4
		END
		ELSE
		BEGIN
		SELECT 0
		END
	END

END





GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoach_CheckChot]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNamKeHoach_CheckChot]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int,
	@NHOM_CHI_TIEU int
AS
BEGIN
	IF(@STATUS_ID = 21 or @STATUS_ID = 51 )
	BEGIN
		IF(EXISTS(SELECT *
		FROM [NV].[KEHOACH]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=@STATUS_ID and [DON_VI_ID]  = @NHOM_CHI_TIEU))
		BEGIN
		SELECT 0
		END
		ELSE
		BEGIN
		SELECT 1
		END
	END
	ELSE
	BEGIN
	IF EXISTS (SELECT *
		FROM [NV].[KEHOACH]
		WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID=@STATUS_ID and 
		[CHI_TIEU_ID] in (select [CHI_TIEU_ID] from [NV].[DONVI_CHITIEU] where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and  [DON_VI_ID]  = @NHOM_CHI_TIEU and FLAG=1))
	BEGIN
	SELECT 0
	END
	ELSE
	BEGIN
	SELECT 1
	END
	END
END





GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoach_CheckPhanHoi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNamKeHoach_CheckPhanHoi]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int

AS
BEGIN
	select distinct dv.[DON_VI] from [NV].[DONVI_CHITIEU] dc join [DM].[DONVI] dv
		on dv.[DON_VI_ID] = dc.[DON_VI_ID]
		where dc.[NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID and dc.DON_VI_ID  in (SELECT DON_VI_ID from  [DM].[DONVI] where [CO_QUYEN_GIAM_SAT] = 0 or  [CO_QUYEN_GIAM_SAT] = 2)
		and dc.DON_VI_ID not in(select DON_VI_ID from [NV].[KEHOACH] 
							where [NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID and status_id=@STATUS_ID)
END





GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoach_Update]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNamKeHoach_Update]
	@NAM_KE_HOACH_ID int,
	@STATUS_ID int

AS
BEGIN
	IF(@STATUS_ID = 50 )
	BEGIN
		UPDATE [NV].[NAMKEHOACH]
		SET STATUS_ID =@STATUS_ID
		WHERE NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID
	END
	

END





GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoachChiTieu_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNamKeHoachChiTieu_Insert]
	@Table as [dbo].[NamKeHoach_ChiTieu] readonly
AS
BEGIN
	DECLARE @NAMKH int
	SELECT @NAMKH=[NAM_KE_HOACH_ID] from @Table
	DELETE FROM [NV].[NAMKH_CHITIEU] WHERE [NAM_KE_HOACH_ID] = @NAMKH

	INSERT INTO [NV].[NAMKH_CHITIEU]([NAM_KE_HOACH_ID], [CHI_TIEU_ID])
	SELECT [NAM_KE_HOACH_ID], [CHI_TIEU_ID] FROM @Table
END





GO
/****** Object:  StoredProcedure [dbo].[spNamKeHoachChiTieu_Update]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNamKeHoachChiTieu_Update]
	@Table as [dbo].[NamKeHoach_ChiTieu] readonly
AS
BEGIN
	UPDATE [dbo].[NAMKH_CHITIEU]
	set NAM_KE_HOACH_ID = t.[NAM_KE_HOACH_ID],
	CHI_TIEU_ID = t.[CHI_TIEU_ID]
	from  [dbo].[NAMKH_CHITIEU] n join @Table t on n.ID = t.ID
	
END





GO
/****** Object:  StoredProcedure [dbo].[spNamkehoch_Getbefore]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNamkehoch_Getbefore]
	@NAM_KE_HOACH_ID int

AS
BEGIN
	DECLARE @NAME INT
	SELECT @NAME = [NAM_KE_HOACH] FROM  [NV].[NAMKEHOACH]
	WHERE [NAM_KE_HOACH_ID]=@NAM_KE_HOACH_ID
	SELECT [NAM_KE_HOACH_ID]
	FROM [NV].[NAMKEHOACH]
	WHERE [NAM_KE_HOACH] = (@NAME-1)
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_Delete]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNguoiDung_Delete]
	@NGUOI_DUNG_ID int
AS
BEGIN
	UPDATE [DM].[NGUOIDUNG]
	SET DELETED = 1
	WHERE [NGUOI_DUNG_ID] = @NGUOI_DUNG_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_Get_List_ChiTieu]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1
-- Create date: 20160414
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[spNguoiDung_Get_List_ChiTieu]
	@TAI_KHOAN varchar(250)
AS
BEGIN
	SELECT d.*
  FROM [DM].[NGUOIDUNG] u
  JOIN [NV].[NGUOIDUNG_CHITIEU] m
  on u.[NGUOI_DUNG_ID] = m.NGUOI_DUNG_ID
  JOIN [DM].CHITIEU d
  on m.CHI_TIEU_ID = d.CHI_TIEU_ID
  WHERE u.[TAI_KHOAN] = @TAI_KHOAN
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_Get_List_DonVi]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1
-- Create date: 20160414
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[spNguoiDung_Get_List_DonVi]
	@TAI_KHOAN varchar(250)
AS
BEGIN
	SELECT d.*
  FROM [DM].[NGUOIDUNG] u
  JOIN [NV].[NGUOIDUNG_DONVI] m
  on u.[NGUOI_DUNG_ID] = m.NGUOI_DUNG_ID
  JOIN [DM].DONVI d
  on m.DON_VI_ID = d.DON_VI_ID
  WHERE u.[TAI_KHOAN] = @TAI_KHOAN
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_Get_PM2]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1
-- Create date: 20160414
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNguoiDung_Get_PM2]
	@TAI_KHOAN varchar(250)
AS
BEGIN
	SELECT [NGUOI_DUNG_ID]
      ,[HO_VA_TEN]
      ,[TAI_KHOAN]
      ,[DIEN_THOAI]
      ,[DELETED]
      ,[LAST_UPDATED]
  FROM [DM].[NGUOIDUNG]
  WHERE [TAI_KHOAN] = @TAI_KHOAN
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_GetAdminById]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[spNguoiDung_GetAdminById]
	@NGUOI_DUNG_ID int
AS
BEGIN
	SELECT u.[NGUOI_DUNG_ID]
      ,u.[HO_VA_TEN]
      ,u.[TAI_KHOAN]
      ,u.[DIEN_THOAI]
      ,u.[DELETED]
      ,u.[LAST_UPDATED]
	  ,m.[DON_VI_ID]
	  ,m1.NHOM_DON_VI_ID
  FROM [DM].[NGUOIDUNG] u
		JOIN [NV].[NGUOIDUNG_DONVI] m
		on u.NGUOI_DUNG_ID = m.NGUOI_DUNG_ID
		JOIN [DM].[DONVI] d
		on m.[DON_VI_ID] = d.[DON_VI_ID]
		JOIN [NV].DONVI_NHOMDONVI m1
		on d.DON_VI_ID = m1.DON_VI_ID
  WHERE u.[NGUOI_DUNG_ID] = @NGUOI_DUNG_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_GetById]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNguoiDung_GetById]
	@NGUOI_DUNG_ID int
AS
BEGIN
	SELECT [NGUOI_DUNG_ID]
      ,[HO_VA_TEN]
      ,[TAI_KHOAN]
      ,[DIEN_THOAI]
      ,[DELETED]
      ,[LAST_UPDATED]
  FROM [DM].[NGUOIDUNG]
  WHERE [NGUOI_DUNG_ID] = @NGUOI_DUNG_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNguoiDung_GetListPaging]
	@HO_VA_TEN nvarchar(250)
      ,@TAI_KHOAN nvarchar(250)
      ,@DIEN_THOAI nvarchar(50)
      ,@DELETED int
	  ,@IS_ADMIN int
	  ,@DON_VI_ID int
	  ,@PAGE_INDEX INT
	  ,@PAGE_SIZE INT
AS
BEGIN
	WITH t1 AS (
		SELECT
			  ROW_NUMBER() OVER(ORDER BY n.[NGUOI_DUNG_ID] DESC) AS NUMBER 
			  ,n.[NGUOI_DUNG_ID]
			  ,[HO_VA_TEN]
			  ,[TAI_KHOAN]
			  ,[DIEN_THOAI]
			  ,[DELETED]
			  ,[LAST_UPDATED]
		  FROM [DM].[NGUOIDUNG] n join [NV].[NGUOIDUNG_DONVI] d
		  ON n.NGUOI_DUNG_ID = d.NGUOI_DUNG_ID
		  WHERE (@DON_VI_ID = 0 OR d.DON_VI_ID = @DON_VI_ID) AND
		       (@HO_VA_TEN IS NULL OR LOWER([HO_VA_TEN]) LIKE '%' + LOWER(@HO_VA_TEN) + '%' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI) AND
			   (@TAI_KHOAN IS NULL OR LOWER([TAI_KHOAN]) LIKE '%' + LOWER(@TAI_KHOAN) + '%' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI) AND
			   (@DIEN_THOAI IS NULL OR [DIEN_THOAI] LIKE '%'+@DIEN_THOAI+'%') AND
			   (@DELETED = 2 OR DELETED = @DELETED) AND
			   (IS_ADMIN = @IS_ADMIN) AND
			   [DELETED] = 0
	)

	SELECT t1.*, (SELECT COUNT(*) FROM t1) AS RECORD_COUNT
	FROM t1
	WHERE NUMBER BETWEEN (@PAGE_INDEX * @PAGE_SIZE + 1) AND ((@PAGE_INDEX + 1) * @PAGE_SIZE)
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDung_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spNguoiDung_InsertUpdate]
	@NGUOI_DUNG_ID int,
	@HO_VA_TEN nvarchar(250),
	@TAI_KHOAN nvarchar(250),
	@DIEN_THOAI nvarchar(50),
	@IS_ADMIN int,
	@DON_VI_ID int
AS
BEGIN
	
	if(@NGUOI_DUNG_ID = 0)
		BEGIN
			INSERT INTO [DM].[NGUOIDUNG]
				   ([HO_VA_TEN]
				   ,[TAI_KHOAN]
				   ,[DIEN_THOAI]
				   ,[DELETED]
				   ,[LAST_UPDATED]
				   ,[IS_ADMIN])
			 VALUES
				   (@HO_VA_TEN
				   ,@TAI_KHOAN
				   ,@DIEN_THOAI
				   ,0
				   ,getdate()
				   ,@IS_ADMIN)
			DECLARE @NGUOIDUNG_ID int;
			SELECT @NGUOIDUNG_ID= @@IDENTITY
			INSERT INTO [NV].[NGUOIDUNG_DONVI](
				[NGUOI_DUNG_ID],
				[DON_VI_ID]
			)
			VALUES(
				@NGUOIDUNG_ID,
				@DON_VI_ID
			)

		END
	else 
		BEGIN
			UPDATE [DM].[NGUOIDUNG]
		    SET [HO_VA_TEN] = @HO_VA_TEN		
				,[TAI_KHOAN] = @TAI_KHOAN  
			    ,[DIEN_THOAI] = @DIEN_THOAI  
				,[LAST_UPDATED] = getdate()
			 WHERE [NGUOI_DUNG_ID] = @NGUOI_DUNG_ID
		END
END




GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungAdmin_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1
-- Create date: 20160420
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNguoiDungAdmin_GetListPaging]
	@HO_VA_TEN nvarchar(250)
      ,@TAI_KHOAN nvarchar(250)
      ,@DIEN_THOAI nvarchar(50)
      ,@DELETED int
	  ,@IS_ADMIN int
	  ,@NHOM_DON_VI_ID int
	  ,@DON_VI_ID int
	  ,@PAGE_INDEX INT
	  ,@PAGE_SIZE INT
AS
BEGIN
	WITH t1 AS (
		SELECT
			  ROW_NUMBER() OVER(ORDER BY u.[NGUOI_DUNG_ID] DESC) AS NUMBER 
			  ,u.[NGUOI_DUNG_ID]
			  ,u.[HO_VA_TEN]
			  ,u.[TAI_KHOAN]
			  ,u.[DIEN_THOAI]
			  ,u.[DELETED]
			  ,u.[LAST_UPDATED]
			  ,m.[DON_VI_ID]
			  ,d.DON_VI AS TEN_DON_VI
			  ,d.NHOM_DON_VI_ID
		  FROM [DM].[NGUOIDUNG] u
		  JOIN [NV].[NGUOIDUNG_DONVI] m
		  on u.NGUOI_DUNG_ID = m.NGUOI_DUNG_ID
		  JOIN [DM].[DONVI] d
		  on m.[DON_VI_ID] = d.[DON_VI_ID]
		  JOIN [NV].DONVI_NHOMDONVI m1
		  on d.DON_VI_ID = m1.DON_VI_ID and m1.IS_CO_BAN = 1
		  WHERE 
		       (@HO_VA_TEN IS NULL OR LOWER([HO_VA_TEN]) LIKE '%' + LOWER(@HO_VA_TEN) + '%' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI) AND
			   (@TAI_KHOAN IS NULL OR LOWER([TAI_KHOAN]) LIKE '%' + LOWER(@TAI_KHOAN) + '%' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI) AND
			   (@DIEN_THOAI IS NULL OR [DIEN_THOAI] LIKE '%'+@DIEN_THOAI+'%') AND
			   (@DELETED = 2 OR DELETED = @DELETED) AND
			   (IS_ADMIN = @IS_ADMIN) AND
			   (@NHOM_DON_VI_ID = 0 OR m1.NHOM_DON_VI_ID = @NHOM_DON_VI_ID) AND
			   (@DON_VI_ID = 0 OR m1.DON_VI_ID = @DON_VI_ID) AND
			   [DELETED] = 0
	)

	SELECT t1.*, (SELECT COUNT(*) FROM t1) AS RECORD_COUNT
	FROM t1
	WHERE NUMBER BETWEEN (@PAGE_INDEX * @PAGE_SIZE + 1) AND ((@PAGE_INDEX + 1) * @PAGE_SIZE)
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungChiTieu_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160413
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[spNguoiDungChiTieu_GetListPaging]
	@NGUOI_DUNG_ID int,
	@PAGE_INDEX int,
	@PAGE_SIZE int
AS
BEGIN
	WITH t1 AS (
		SELECT ROW_NUMBER() OVER(ORDER BY ndct.ID DESC) AS NUMBER,
		    ndct.ID, nd.TAI_KHOAN, nd.HO_VA_TEN, ct.TEN_CHI_TIEU
		FROM [NV].[NGUOIDUNG_CHITIEU] ndct
			join [DM].[NGUOIDUNG] nd on ndct.NGUOI_DUNG_ID = nd.NGUOI_DUNG_ID
			join [DM].[CHITIEU] ct on ndct.CHI_TIEU_ID = ct.CHI_TIEU_ID
		WHERE ndct.NGUOI_DUNG_ID = @NGUOI_DUNG_ID
	)

	SELECT *, (SELECT COUNT(*) FROM t1) AS RECORD_COUNT
	FROM t1
	WHERE NUMBER BETWEEN (@PAGE_INDEX * @PAGE_SIZE + 1) AND ((@PAGE_INDEX + 1) * @PAGE_SIZE)
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungChiTieu_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160413
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[spNguoiDungChiTieu_Insert]
	@NGUOI_DUNG_ID int,
	@CHI_TIEU_ID int
AS
BEGIN
	INSERT INTO [NV].[NGUOIDUNG_CHITIEU](
		[NGUOI_DUNG_ID],
		[CHI_TIEU_ID]
	)
	VALUES(
		@NGUOI_DUNG_ID,
		@CHI_TIEU_ID
	)
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungDonVi_Delete]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNguoiDungDonVi_Delete]
	@ID int
AS
BEGIN
	DELETE FROM [NV].[NGUOIDUNG_DONVI]
	WHERE [ID] = @ID
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungDonVi_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[spNguoiDungDonVi_GetListPaging]
	@HO_VA_TEN nvarchar(250),
	@TAI_KHOAN nvarchar(250),
	@DON_VI_ID int,
	@PAGE_INDEX int,
	@PAGE_SIZE int
AS
BEGIN
	WITH t1 AS (
		SELECT ROW_NUMBER() OVER(ORDER BY nddv.ID DESC) AS NUMBER,
		    nddv.ID, nd.TAI_KHOAN, nd.HO_VA_TEN, dv.DON_VI FROM [NV].[NGUOIDUNG_DONVI] nddv
			join [DM].[NGUOIDUNG] nd on nddv.NGUOI_DUNG_ID = nd.NGUOI_DUNG_ID
			join [DM].[DONVI] dv on nddv.DON_VI_ID = dv.DON_VI_ID
		WHERE (@HO_VA_TEN IS NULL OR LOWER(nd.HO_VA_TEN) LIKE LOWER('%'+@HO_VA_TEN +'%') 
													COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI) AND
			  (@TAI_KHOAN IS NULL OR LOWER(nd.TAI_KHOAN) LIKE LOWER('%'+@TAI_KHOAN +'%') 
													COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI) AND
			  (nddv.DON_VI_ID = @DON_VI_ID)
	)

	SELECT *, (SELECT COUNT(*) FROM t1) AS RECORD_COUNT
	FROM t1
	WHERE NUMBER BETWEEN (@PAGE_INDEX * @PAGE_SIZE + 1) AND ((@PAGE_INDEX + 1) * @PAGE_SIZE)
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungDonVi_GetListUser]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160413
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNguoiDungDonVi_GetListUser]
AS
BEGIN
	SELECT * FROM [DM].[NGUOIDUNG] nd
	WHERE nd.NGUOI_DUNG_ID NOT IN (
						SELECT NGUOI_DUNG_ID 
						FROM [NV].[NGUOIDUNG_DONVI])
END





GO
/****** Object:  StoredProcedure [dbo].[spNguoiDungDonVi_Insert]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: 20160413
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNguoiDungDonVi_Insert]
	@NGUOI_DUNG_ID int,
	@DON_VI_ID int
AS
BEGIN
	INSERT INTO [NV].[NGUOIDUNG_DONVI](
		[NGUOI_DUNG_ID],
		[DON_VI_ID]
	)
	VALUES(
		@NGUOI_DUNG_ID,
		@DON_VI_ID
	)
END





GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_Delete]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong	
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[spNhomDonVi_Delete]
	@NHOM_DON_VI_ID int
AS
BEGIN
	DELETE FROM [DM].[NHOMDONVI]
      WHERE [NHOM_DON_VI_ID] = @NHOM_DON_VI_ID
END





GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_GetAll]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1	
-- Create date: 20160411
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[spNhomDonVi_GetAll]
AS
BEGIN
	SELECT 
			[NHOM_DON_VI_ID]
			,[TEN_NHOM_DON_VI]
			,[GHI_CHU]
		FROM [DM].[NHOMDONVI] n
		
  
END





GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_GetByDonViID]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1	
-- Create date: 20160426
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[spNhomDonVi_GetByDonViID]
	@DON_VI_ID INT
AS
BEGIN
		SELECT n.*, m.DON_VI_ID,
		CASE
			WHEN m.DON_VI_ID IS NULL THEN 0
			ELSE 1
		END AS IS_CHECKED
		FROM [DM].[NHOMDONVI] n
		LEFT JOIN
		(select * from [NV].[DONVI_NHOMDONVI] 
		where DON_VI_ID = @DON_VI_ID AND(@DON_VI_ID = 0 OR DON_VI_ID = @DON_VI_ID)
		) m
		on n.NHOM_DON_VI_ID = m.NHOM_DON_VI_ID

		

		
END





GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_GetById]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong	
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNhomDonVi_GetById]
	@NHOM_DON_VI_ID int
AS
BEGIN
	SELECT 
			[NHOM_DON_VI_ID]
			,[TEN_NHOM_DON_VI]
			,[IS_CO_BAN]
			,[GHI_CHU]
		FROM [DM].[NHOMDONVI] n
		WHERE [NHOM_DON_VI_ID] = @NHOM_DON_VI_ID
  
END





GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_GetByType]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		phunx1	
-- Create date: 20160421
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNhomDonVi_GetByType]
	@IS_CO_BAN INT
AS
BEGIN
	SELECT 
			[NHOM_DON_VI_ID]
			,[TEN_NHOM_DON_VI]
			,[GHI_CHU]
			,[IS_CO_BAN]
		FROM [DM].[NHOMDONVI] n
		where (IS_CO_BAN = 0 AND @IS_CO_BAN = 0) OR (IS_CO_BAN = 1 AND @IS_CO_BAN = 1 AND [NHOM_DON_VI_ID] <> 1019)
		OR @IS_CO_BAN = 2

		
  
END





GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_GetListPaging]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong	
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNhomDonVi_GetListPaging]
	@TEN_NHOM_DON_VI nvarchar(255),
	@IS_CO_BAN INT,
	@PAGE_INDEX INT,
	@PAGE_SIZE INT
AS
BEGIN
	with t1 as
	(
		SELECT 
			ROW_NUMBER() OVER(ORDER BY n.[NHOM_DON_VI_ID] desc) AS NUMBER
			,[NHOM_DON_VI_ID]
			,[TEN_NHOM_DON_VI]
			,[GHI_CHU]
			,[IS_CO_BAN]
		FROM [DM].[NHOMDONVI] n
		WHERE (@TEN_NHOM_DON_VI IS NULL OR 
			  LOWER([TEN_NHOM_DON_VI]) LIKE LOWER(@TEN_NHOM_DON_VI)
			  COLLATE SQL_Latin1_General_Cp1_CI_AI) -- khong phan biet co dau khong dau
			  AND (@IS_CO_BAN = 2 OR [IS_CO_BAN] = @IS_CO_BAN)
  )

  SELECT t1.*, (SELECT COUNT(*) FROM t1 ) AS RECORD_COUNT
  FROM t1
  WHERE NUMBER BETWEEN (@PAGE_INDEX * @PAGE_SIZE + 1) AND 
					   (@PAGE_INDEX + 1) * @PAGE_SIZE
END





GO
/****** Object:  StoredProcedure [dbo].[spNhomDonVi_InsertUpdate]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong	
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spNhomDonVi_InsertUpdate]
	@NHOM_DON_VI_ID int,
	@TEN_NHOM_DON_VI nvarchar(255),
	@IS_CO_BAN int,
	@GHI_CHU nvarchar(255)
AS
BEGIN
	if(@NHOM_DON_VI_ID <= 0)
		BEGIN
			INSERT INTO [DM].[NHOMDONVI]
				   ([TEN_NHOM_DON_VI]
				   ,[IS_CO_BAN]
				   ,[GHI_CHU])
			 VALUES
				   (@TEN_NHOM_DON_VI
				   ,@IS_CO_BAN
				   ,@GHI_CHU)
		END
	else
		BEGIN
			UPDATE [DM].[NHOMDONVI] 
			SET [TEN_NHOM_DON_VI] = @TEN_NHOM_DON_VI,
			[GHI_CHU] = @GHI_CHU,
			[IS_CO_BAN] = @IS_CO_BAN
			where NHOM_DON_VI_ID = @NHOM_DON_VI_ID
		END
END





GO
/****** Object:  StoredProcedure [dbo].[spStatus_GetAll]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spStatus_GetAll]

AS
BEGIN
	SELECT * FROM [DM].[STATUS]
END





GO
/****** Object:  StoredProcedure [dbo].[spStatus_GetByID]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		huennb
-- Create date: 20160402
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spStatus_GetByID]
	@STATUS_ID int
AS
BEGIN
	SELECT * FROM [DM].[STATUS]
	WHERE [STATUS_ID]=@STATUS_ID
END





GO
/****** Object:  StoredProcedure [dbo].[tool_SelectKeHoachNotInDonViChiTieu]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		HuyNV23
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[tool_SelectKeHoachNotInDonViChiTieu]
	-- Add the parameters for the stored procedure here
	@NAM_KE_HOACH_ID int,
	@STATUS_ID smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    select a1.[DON_VI_ID],a1.[CHI_TIEU_ID], a2.[TEN_CHI_TIEU_CAP1], a2.[TEN_CHI_TIEU_CAP2] , a2.TEN_CHI_TIEU 
	from [NV].[DONVI_CHITIEU] as a1
	inner join [DM].[CHITIEU] as a2 on a1.CHI_TIEU_ID = a2.CHI_TIEU_ID
	where [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and a1.[FLAG] = 0
	and a2.[TYPE] = 1
	and a1.[DON_VI_CHI_TIEU_ID] not in
	(select dvct.[DON_VI_CHI_TIEU_ID]
	from [NV].[DONVI_CHITIEU] as dvct
	inner join [NV].[KEHOACH] as kh on kh.CHI_TIEU_ID = dvct.CHI_TIEU_ID and kh.DON_VI_ID = dvct.DON_VI_ID
	where dvct.[NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID
	and dvct.FLAG = 0
	and kh.NAM_KE_HOACH_ID = @NAM_KE_HOACH_ID
	and kh.[STATUS_ID] = @STATUS_ID)
END




GO
/****** Object:  UserDefinedFunction [dbo].[ConcatFunction]    Script Date: 24/05/2016 12:49:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ConcatFunction]
(
  @DON_VI_ID SYSNAME
)
RETURNS NVARCHAR(MAX)
  
AS 
BEGIN
  DECLARE @s NVARCHAR(MAX);
 
  SELECT @s = COALESCE(@s + N', ', N'') + TEN_NHOM_DON_VI
    FROM [DM].DONVI d
	JOIN [NV].DONVI_NHOMDONVI m
	on d.DON_VI_ID = m.DON_VI_ID
	LEFT JOIN [DM].NHOMDONVI n
	on m.NHOM_DON_VI_ID = n.NHOM_DON_VI_ID
	WHERE d.DON_VI_ID = @DON_VI_ID
	ORDER BY d.DON_VI_ID;
 
  RETURN (@s);
END

GO
